Multiple Multi Select list boxes VBA/ SQL Query

Question

I think I am almost there with this project but I need some help with the SQL part of my code. I have limited experience with the SQL part and need some help with the syntax as I am getting 'Run-Time error 3075 Syntax error missing operator in query expression'. And if I am incoorporating the SQL correctly with the Query SQL. The SQL from the query table is.

SELECT warehouseMetrics.Date, warehouseMetrics.Client, Teammates.[User ID], JobDescriptions.[Job Code Description], warehouseMetrics.[Perf Percent],     Supervisors.Supervisor
FROM ((Teammates INNER JOIN warehouseMetrics ON Teammates.[User ID] = warehouseMetrics.[User ID]) INNER JOIN Supervisors ON Teammates.Supervisor =     Supervisors.Supervisor) INNER JOIN JobDescriptions ON warehouseMetrics.[Job Code     Description] = JobDescriptions.[Job Code Description]
WHERE (((warehouseMetrics.Date) Between [Forms]![MasterForm]![txtBeginDate] And [Forms]![MasterForm]![txtEndDate]))
ORDER BY warehouseMetrics.Date;

And the code that runs the query is...

Dim Q As QueryDef, DB As Database

Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Dim Criteria4 As String
Dim itm As Variant
Dim strSQL As String

Set DB = CurrentDb()
Set QueryDef = DB.QueryDefs("MasterReportQuery")

For Each itm In Me!lstSupervisor.ItemsSelected
    Criteria1 = Criteria1 & "," & Me!lstSupervisor.ItemData(itm) & ""
Next itm

For Each itm In Me!lstClient.ItemsSelected
    Criteria2 = Criteria2 & "," & Me!lstClient.ItemData(itm) & ""
Next itm

For Each itm In Me!lstTeammates.ItemsSelected
    Criteria3 = Criteria3 & "," & Me!lstTeammates.ItemData(itm) & ""
Next itm

For Each itm In Me!lstJobs.ItemsSelected
    Criteria4 = Criteria4 & "," & Me!lstJobs.ItemData(itm) & ""
Next itm

If Len(Criteria1) = 0 Or Len(Criteria2) = 0 Or Len(Criteria3) = 0 Or Len(Criteria4) = 0 Then
MsgBox "You did not select anything"
Exit Sub
End If

Criteria1 = Right(Criteria1, Len(Criteria1) - 1)
Criteria2 = Right(Criteria2, Len(Criteria2) - 1)
Criteria3 = Right(Criteria3, Len(Criteria3) - 1)
Cirteria4 = Right(Criteria4, Len(Criteria4) - 1)

MsgBox (Criteria1)
MsgBox (Criteria2)
MsgBox (Criteria3)
MsgBox (Criteria4)

strSQL = "SELECT warehouseMetrics.Date, warehouseMetrics.Client, Teammates.  [User ID]," & _
"JobDescriptions.[Job Code Description], warehouseMetrics.[Perf Percent],   Supervisors.Supervisor" & _
"FROM ((Teammates INNER JOIN warehouseMetrics ON Teammates.[User ID] = warehouseMetrics.[User ID])" & _
"INNER JOIN Supervisors ON Teammates.Supervisor = Supervisors.Supervisor) INNER JOIN" & _
"JobDescriptions ON warehouseMetrics.[Job Code Description] = JobDescriptions.[Job Code Description]" & _
"WHERE warehouseMetrics.Client IN (" & Criteria2 & ")" & _
"Supervisors.Supervisor IN (" & Criteria1 & ")" & _
"Teammates.[User ID] IN (" & Criteria3 & ")" & _
"JobDescriptions.[Job Code Description] IN (" & Criteria4 & ");"

QueryDef.SQL = strSQL
DoCmd.OpenQuery "MasterReportQuery"
Set DB = Nothing
Set QueryDef = Nothing

End Sub

Have you checked your final strSQL to find out if it's valid SQL? For example, I'm wondering if your criteria1, 2, 3 etc will end up as a string starting with a comma, since the first loop will concatenate a comma to an empty variable. — Ambulare, Oct 26 '16 at 16:51
Paste `strSQL` into a new query in SQL view. Try to switch to datasheet view. Access will point out the problematic part of the SQL. [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) — Andre, Oct 28 '16 at 09:25

finjo · Answer 1 · 2016-10-28T08:45:59.583

The Criteria variables need to be wrapped with single quotes if they are of a text datatype. Since you're looping through multiple items and using an IN statement in the SQL for each Criteria, you probably need to add these in the for loops:

For Each itm In Me!lstSupervisor.ItemsSelected
    Criteria1 = Criteria1 & ", '" & & Me!lstSupervisor.ItemData(itm) & "'"
Next itm

For Each itm In Me!lstClient.ItemsSelected
    Criteria2 = Criteria2 & ", '" & Me!lstClient.ItemData(itm) & "'"
Next itm

For Each itm In Me!lstTeammates.ItemsSelected
    Criteria3 = Criteria3 & ", '" & Me!lstTeammates.ItemData(itm) & "'"
Next itm

For Each itm In Me!lstJobs.ItemsSelected
    Criteria4 = Criteria4 & ", '" & Me!lstJobs.ItemData(itm) & "'"
Next itm

There are also several issues with the spacing of your SQL statement:

strSQL = "SELECT warehouseMetrics.Date, warehouseMetrics.Client, Teammates.[User ID], " & _
"JobDescriptions.[Job Code Description], warehouseMetrics.[Perf Percent],   Supervisors.Supervisor " & _
"FROM ((Teammates INNER JOIN warehouseMetrics ON Teammates.[User ID] = warehouseMetrics.[User ID]) " & _
"INNER JOIN Supervisors ON Teammates.Supervisor = Supervisors.Supervisor) INNER JOIN " & _
"JobDescriptions ON warehouseMetrics.[Job Code Description] = JobDescriptions.[Job Code Description] " & _
"WHERE warehouseMetrics.Client IN (" & Criteria2 & ") " & _
"Supervisors.Supervisor IN (" & Criteria1 & ") " & _
"Teammates.[User ID] IN (" & Criteria3 & ") " & _
"JobDescriptions.[Job Code Description] IN (" & Criteria4 & ");"`

I made the suggested edits but the runtime error I'm getting is still the same. I have a feeling that the strSQL is not properly formatted but with my lack of experience with it, I am not sure how to know what is wrong. `Run-time error '3075': Syntax Error (missing operator) in query expression 'Supervisors.SupervisorFrom ((Treammates INNER JOIN warehouseMetrics ON Teammates.[User ID] = warehouseMetrics.[User ID]) INNER JOIN Supervisors ON Teammates.Supervisors.Supervisor) IINER JOIN JobDescriptions ON warehouseMetrics.[Job Code Description] = JobDescriptions.[Job Code Description] Where` — lPaclMan, Oct 27 '16 at 17:56
I've edited my original answer, there are a number of issues with your SQL statement that I didn't notice originally - you have carried your query over to the next line without a space between the words, which is why you're getting 'Supervisors.SupervisorFrom'. Can you try again with the edited code? — finjo, Oct 28 '16 at 08:48

Multiple Multi Select list boxes VBA/ SQL Query

1 Answers1