CODEWITHSUNDEEP
ms-access
Chronix3
Chronix3

Reputation: 621

MS Query Criteria is overlooked by VBA

I've got the following SQL statement in a VBA module:

dbs.Execute "INSERT INTO PA2001CustomReportingTable ([Personnel No], [Subtype], [Start Date], [End Date], [CalDays]) " & _
"SELECT [Personnel No], [Subtype], [Start Date], [End Date], [CalDays] FROM PA2001 " & _
"WHERE ([PA2001].[Cal  days] > 0) AND ([PA2001].[Start Date]>=#" & StartDatePA2001Lower & "#) AND ([PA2001].[End Date]<=#" & EndDatePA2001Upper & "#) AND (" & GetListOfInclusionTypes() & ");"

The GetListOfInclusionTypes call function is a simple string builder that uses the user selection on a list-box on a user-form to build the string.

Function GetListOfInclusionTypes() As String

Dim i As Integer
Dim isFirst As Boolean
isFirst = True

'Convert Listbox into String of Selected Leave Types
For i = 0 To Forms("MainReport").Controls("SelLeaveTypes").ListCount - 1

    If Forms("MainReport").Controls("SelLeaveTypes").Selected(i) Then
        If isFirst = True Then
        GetListOfInclusionTypes = "[PA2001].[Subtype]=" & Chr(34) & Forms("MainReport").Controls("SelLeaveTypes").Column(0, i) & Chr(34)
        isFirst = False
        Else
        GetListOfInclusionTypes = GetListOfInclusionTypes & " OR [PA2001].[Subtype]=" & Chr(34) & Forms("MainReport").Controls("SelLeaveTypes").Column(0, i) & Chr(34)
        End If

    End If
Next i

End Function

A sample string that it builds is:

[PA2001].[Subtype] = "0100" Or [PA2001].[Subtype] = "0200" Or [PA2001].[Subtype] = "0300"

However, once the Insert into SQL statement is run; data is inserted into the new table that doesn't fall into the date range specified in the SQL Where clause! I'm not seeing where the mistake is..

Upvotes: 1

Views: 121

Answers (1)

HansUp
HansUp

Reputation: 97101

I can't spot the problem with that INSERT statement. The WHERE clause references a field with 2 spaces in its name, [PA2001].[Cal days], which seems suspicious at first blush. However, if that weren't the actual field name, you should get an error ... which you're not.

Give yourself an opportunity to examine the completed statement you're asking the db engine to execute.

Dim strInsert As String
strInsert = "INSERT INTO PA2001CustomReportingTable ([Personnel No], [Subtype], [Start Date], [End Date], [CalDays]) " & _
"SELECT [Personnel No], [Subtype], [Start Date], [End Date], [CalDays] FROM PA2001 " & _
"WHERE ([PA2001].[Cal  days] > 0) AND ([PA2001].[Start Date]>=#" & StartDatePA2001Lower & "#) AND ([PA2001].[End Date]<=#" & EndDatePA2001Upper & "#) AND (" & GetListOfInclusionTypes() & ");"
Debug.Print strInsert
dbs.Execute strInsert, dbFailOnError

Then you can view the completed statement in the Immediate window (go there with Ctrl+g). You can copy the statement text and paste it into SQL View of a new Access query for testing. Paste that statement into your question if you need further help.

As a side point, these 2 query conditions are equivalent:

[PA2001].[Subtype] = "0100" Or [PA2001].[Subtype] = "0200" Or [PA2001].[Subtype] = "0300"
[PA2001].[Subtype] In ("0100","0200","0300")

That doesn't have anything to do with why your query isn't doing what you want. But the second form is more concise and might make trouble-shooting the statement easier. I don't know if it's worth changing your GetListOfInclusionTypes function, though.

Upvotes: 1

Related Questions