Run-time error '3075': 1

[347pg]

I have a form in my database where people select multiple query inputs from multiple list boxes. When they push the "Update" button, these inputs should populate the query and the query should run (yeah right!). Well I'm getting a run-time error 3075 that says:
"Syntax error (missing operator) in query expression '(((IT_MTL.[Task ID]) Like '*332*' OR Like '*71*') AND ((IT_MTL.[Task ID]) Like '*RQ*' OR Like '*IN*') AND ((IT_MTL.[Task ID]) Like '*PM*' OR Like '*NE*')) AND ((IT_MTL.[Mod]) Like '*L*')'.

So it looks like my list box info is writing to the query, but it won't run. When I debug, it highlights the 3rd to last line (i.e. qdf.SQL = strSQL)

Can anyone see where I'm missing it?
Thanks in advance for any and all help.
Here is my code:



Option Compare Database

Private Sub cmdApplyChoices_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim strSQLProj As String
Dim strSQLPhase As String
Dim strSQLSkill As String
Dim strSQLMod As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("IT_MTL Query")
'=======================================
'Below we set the initial string to null
'=======================================

strCriteria = ""
strSQL = ""
strSQLProj = ""

'=======================================================
'Below we'll get items selected from the Project listbox
'=======================================================
If Me!ProjList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!ProjList.ItemsSelected
strSQLProj = strSQLProj & " Like " & "'*" & Me.ProjList.ItemData(varItem) & "*'" & " OR "
Next varItem
strSQLProj = Left(strSQLProj, Len(strSQLProj) - 4)

End If

strSQLPhase = ""

'=====================================================
'Below we'll get items selected from the Phase listbox
'=====================================================
If Me!PhaseList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!PhaseList.ItemsSelected
strSQLPhase = strSQLPhase & " Like " & "'*" & Me!PhaseList.ItemData(varItem) & "*'" & " OR "
Next varItem
strSQLPhase = Left(strSQLPhase, Len(strSQLPhase) - 4)

End If

strSQLSkill = ""

'========================================================
'Below we'll get items selected from the Skillset listbox
'========================================================
If Me!SkillList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!SkillList.ItemsSelected
strSQLSkill = strSQLSkill & " Like " & "'*" & Me!SkillList.ItemData(varItem) & "*'" & " OR "
Next varItem
strSQLSkill = Left(strSQLSkill, Len(strSQLSkill) - 4)

End If

strSQLMod = ""

'===================================================
'Below we'll get items selected from the Mod listbox
'===================================================
If Me!ModList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!ModList.ItemsSelected
strSQLMod = strSQLMod & " Like " & "'*" & Me!ModList.ItemData(varItem) & "*'" & " OR "
Next varItem
strSQLMod = Left(strSQLMod, Len(strSQLMod) - 4)

End If

'===================================================
'Below we'll combine all the separate criteria into
'one string called "strSQL"
'===================================================

strSQL = "SELECT IT_MTL.[Task ID], IT_MTL.Task, IT_MTL.ProposedHours, IT_MTL.NegHours, IT_MTL.[Mod] FROM IT_MTL " & _
"WHERE (((IT_MTL.[Task ID])" & strSQLProj & ") And " & "((IT_MTL.[Task ID])" & strSQLPhase & ") And " & "((IT_MTL.[Task ID])" & strSQLSkill & ")) AND " & "((IT_MTL.[Mod])" & strSQLMod & ");"

qdf.SQL = strSQL
DoCmd.OpenQuery "IT_MTL Query"


End Sub

 

[PHV]

Replace this:
strSQLProj = strSQLProj & " Like " & "'*" & Me.ProjList.ItemData(varItem) & "*'" & " OR "
with this:
strSQLProj = strSQLProj & "IT_MTL.[Task ID] Like '*" & Me.ProjList.ItemData(varItem) & "*' OR "

Similar for the others listbox.

And then replace this:
"WHERE (((IT_MTL.[Task ID])" & strSQLProj & ") And " & "((IT_MTL.[Task ID])" & strSQLPhase & ") And " & "((IT_MTL.[Task ID])" & strSQLSkill & ")) AND " & "((IT_MTL.[Mod])" & strSQLMod & ");"
with this:
"WHERE (" & strSQLProj & ") AND (" & strSQLPhase & ") AND (" & strSQLSkill & ") AND (" & strSQLMod & ");"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[347pg]

EXCELLENT!! That worked like a charm. Appreciate all the help. I'll be back. I have to modify the code to work when the user selects nothing.
Thanks again
pw