Run-Time Error 3075

[jinman1883]

Hi,

I'm a complete novice at code and I get a run-time error 3075. The error reads -

'Syntax error (missing operator) in query expression 'Gazetteer.Status IN(,'Current') AND Gazetteer.Geographytype IN(,'Census,'Economic')'

What I am trying to do -

I want to put 3 multi-select list boxes on one form, these represent the values from 3 fields in a table. The user can then select any comination of values from the 3 lists and I want to use these as my selection criteria in one query and run the query through one command button, that when pressed will show the results.

So far, I have my 2 multi select list boxes so far and have written this code:



Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria1 As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("QryGazetteer")
strCriteria = " "
strCriteria1 = " "
For Each varItem In Me!LstStatus.ItemsSelected
strCriteria = strCriteria & ",'" & Me!LstStatus.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!LstGeog.ItemsSelected
strCriteria1 = strCriteria1 & ",'" & Me!LstGeog.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)
strSQL = "SELECT * FROM Gazetteer " & _
"WHERE Gazetteer.Status IN(" & strCriteria & ") " & _
"AND Gazetteer.Geographytype IN(" & strCriteria1 & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "QryGazetteer"
Set db = Nothing
Set qdf = Nothing
End Sub


but I get the Run-time error 3075 now. The error appears to be something to do with the way I have created the WHERE and AND statement. I can't see why and need help.

Thanks for your time

 

[Jon K]

Just change these two lines:-
strCriteria = " "
strCriteria1 = " "

to:-
strCriteria = ""
strCriteria1 = ""


That is, delete the space between the double quotes.