Need help with Run-time error 3075

[Vralus]

I have a form in my database where people select multiple query inputs from multiple list boxes but when I try to run it I keep getting a run-time error 3075 that says:
Syntax error (missing operator) in query expression
'tblOffender.[AreaOffice] Like '*'tblOffender.[ReleaseType] Like
'*'tblOffender.[RPV] Like '*'tblOffender.[AtualReleaseDate] 1=1
tblOffender.[IncomingDate] 1=1'.

When I debug, it highlights: qdf.SQL = strSQL

I'm not very knowledgeable about programming (this is my first attempt) but I followed some instructions I found online as accurately as possible. I just can't figure what's wrong.
Could someone please help me with this? I'd very much appreciate it.

Here is my code (in Access 2007):

Private Sub cmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim blnQueryExists As Boolean
Dim varItem As Variant
Dim strArea As String
Dim strType As String
Dim strRPV As String
Dim strSortOrder
Dim strSQL As String
' Check for the existence of the stored query
Set db = CurrentDb
blnQueryExists = False
For Each qdf In db.QueryDefs
If qdf.Name = "qryCustom" Then
blnQueryExists = True
Exit For
End If
Next qdf
' Create the query if it does not already exist
If blnQueryExists = False Then
Set qdf = db.CreateQueryDef("qryCustom")
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryCustom") = acObjStateOpen Then
DoCmd.Close acQuery, "qryCustom"
End If
' Build criteria string for Area
For Each varItem In Me.lstArea.ItemsSelected
strArea = strArea & ",'" & Me.lstArea.ItemData(varItem) & "'"
Next varItem
If Len(strArea) = 0 Then
strArea = "Like '*'"
Else
strArea = Right(strArea, Len(strArea) - 1)
strArea = "IN(" & strArea & ")"
End If
' Build criteria string for Type
For Each varItem In Me.lstType.ItemsSelected
strType = strType & ",'" & Me.lstType.ItemData(varItem) & "'"
Next varItem
If Len(strType) = 0 Then
strType = "Like '*'"
Else
strType = Right(strType, Len(strType) - 1)
strType = "IN(" & strType & ")"
End If
' Build criteria string for RPV
For Each varItem In Me.lstRPV.ItemsSelected
strRPV = strRPV & ",'" & Me.lstRPV.ItemData(varItem) & "'"
Next varItem
If Len(strRPV) = 0 Then
strRPV = "Like '*'"
Else
strRPV = Right(strRPV, Len(strRPV) - 1)
strRPV = "IN(" & strRPV & ")"
End If
' Build sort clause
If Me.cboSortOrder1.Value <> "Not Sorted" Then
strSortOrder = " ORDER BY tblOffender.[" & Me.cboSortOrder1.Value & "]"
If Me.cboSortOrder2.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",tblOffender.[" & Me.cboSortOrder2.Value & "]"
If Me.cboSortOrder3.Value <> "Not Sorted" Then
strSortOrder = strSortOrder & ",tblOffender.[" & Me.cboSortOrder3.Value & "]"
End If
End If
Else
strSortOrder = ""
End If
' Release Date Range
Dim strOut As String
strOut = "1=1 "
If Not IsNull(Me.txtStartOut) Then
strOut = strOut & " AND " & _
" >= #" & Me.txtStartOut & "# "
End If
If Not IsNull(Me.txtEndOut) Then
strOut = strOut & " AND " & _
" <= #" & Me.txtEndOut & "# "
End If
' Incoming Date Range
Dim strIn As String
strIn = "1=1 "
If Not IsNull(Me.txtStartIn) Then
strIn = strIn & " AND " & _
" >= #" & Me.txtStartIn & "# "
End If
If Not IsNull(Me.txtEndIn) Then
strIn = strIn & " AND " & _
" <= #" & Me.txtEndIn & "# "
End If
' Build SQL statement
strSQL = "SELECT tblOffender.* FROM tblOffender " & _
"WHERE tblOffender.[AreaOffice] " & strArea & _
"tblOffender.[ReleaseType] " & strType & _
"tblOffender.[RPV] " & strRPV & _
"tblOffender.[ActualReleaseDate] " & strOut & _
"tblOffender.[IncomingDate] " & strIn & _
strSortOrder & ";"
' Apply the SQL statement to the stored query
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCustom")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing
' Open the Query
DoCmd.OpenQuery "qryCustom"
' Restore screen updating
DoCmd.Echo True
End Sub

 

[dhookom]

I would clean up the code like the following which assume all of the criteria fields are either string/text or dates.

Private Sub cmdOk_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim blnQueryExists As Boolean
Dim varItem As Variant
Dim strArea As String
Dim strType As String
Dim strRPV As String
Dim strSortOrder
Dim strSQL As String
Dim strWhere as String   [COLOR=#A40000]'beginning of where condition[/color]
strWhere = " WHERE 1=1 "
[COLOR=#A40000]' Check for the existence of the stored query[/color]
Set db = CurrentDb
blnQueryExists = False
For Each qdf In db.QueryDefs
	If qdf.Name = "qryCustom" Then
		blnQueryExists = True
		Exit For
	End If
Next qdf
[COLOR=#A40000]' Create the query if it does not already exist[/color]
If blnQueryExists = False Then
	Set qdf = db.CreateQueryDef("qryCustom")
End If
Application.RefreshDatabaseWindow
[COLOR=#A40000]' Turn off screen updating[/color]
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryCustom") = acObjStateOpen Then
	DoCmd.Close acQuery, "qryCustom"
End If
[COLOR=#A40000]' Build criteria string for Area[/color]
For Each varItem In Me.lstArea.ItemsSelected
	strArea = strArea & ",'" & Me.lstArea.ItemData(varItem) & "'"
Next varItem
If Len(strArea) > 0 Then
	strArea = Right(strArea, Len(strArea) - 1)
	strWhere = strWhere & " AND tblOffender.[AreaOffice] IN(" & strArea & ") "
End If
[COLOR=#A40000]' Build criteria string for Type[/color]
For Each varItem In Me.lstType.ItemsSelected
	strType = strType & ",'" & Me.lstType.ItemData(varItem) & "'"
Next varItem
If Len(strType) > 0 Then
	strType = Right(strType, Len(strType) - 1)
	strWhere = strWhere & " AND tblOffender.[ReleaseType] IN(" & strType & ") "
End If
[COLOR=#A40000]' Build criteria string for RPV[/color]
For Each varItem In Me.lstRPV.ItemsSelected
	strRPV = strRPV & ",'" & Me.lstRPV.ItemData(varItem) & "'"
Next varItem
If Len(strRPV) > 0 Then
	strRPV = Right(strRPV, Len(strRPV) - 1)
	strWhere = strWhere & " AND tblOffender.[RPV] IN(" & strRPV & ") "
End If
[COLOR=#A40000]' Build sort clause[/color]
If Me.cboSortOrder1.Value <> "Not Sorted" Then
	strSortOrder = " ORDER BY tblOffender.[" & Me.cboSortOrder1.Value & "]"
	If Me.cboSortOrder2.Value <> "Not Sorted" Then
		strSortOrder = strSortOrder & ",tblOffender.[" & Me.cboSortOrder2.Value & "]"
		If Me.cboSortOrder3.Value <> "Not Sorted" Then
			strSortOrder = strSortOrder & ",tblOffender.[" & Me.cboSortOrder3.Value & "]"
		End If
	End If
 Else
	strSortOrder = ""
End If

[COLOR=#A40000]' Release Date Range[/color]
If Not IsNull(Me.txtStartOut) Then
	strWhere = strWhere & " AND ActualReleaseDate >= #" & Me.txtStartOut & "# "
End If
If Not IsNull(Me.txtEndOut) Then
	strWhere = strWhere & " AND ActualReleaseDate <= #" & Me.txtEndOut & "# "
End If

[COLOR=#A40000]' Incoming Date Range[/color]
If Not IsNull(Me.txtStartIn) Then
	strWhere = strWhere & " AND [IncomingDate] >= #" & Me.txtStartIn & "# "
End If
If Not IsNull(Me.txtEndIn) Then
	strWhere = strWhere & " AND [IncomingDate] <= #" & Me.txtEndIn & "# "
End If
debug.Print "strWhere: " & strWhere
[COLOR=#A40000]' Build SQL statement[/color]
strSQL = "SELECT * FROM tblOffender " & _
	strWhere & _
	strSortOrder & ";"
[COLOR=#A40000]' Apply the SQL statement to the stored query[/color]
Debug.Print "strSQL: " & strSQL
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCustom")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing
[COLOR=#A40000]' Open the Query[/color]
DoCmd.OpenQuery "qryCustom"
[COLOR=#A40000]' Restore screen updating[/color]
DoCmd.Echo True
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Vralus]

It works! Thank you so much!