Access VBA Run-time error '3075': Syntax error (missing operator) in query expression 'SETLMT_RPT1901-1907.RPT_MTH IN ('1907')'. HELP

Question

I have a form parameter in RptMth191, list box with row source "1901"; "1902"; etc. with extended multiselect and a cmdOK button to run the query command below.  Every time I click I get the run-time error above.  I don't see what I'm missing, please help.  Thanks.

Answer 1

We literally don't see it either, since you did not post the code from the button click event.

Answer 2

Sorry, I thought the snip it picture that I posted would be viewable.  Below is the command code:

Option Compare Database

Private Sub cmdOK_Click()

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim varItem As Variant

Dim strCriteria As String

Dim strSQL As String

Set db = CurrentDb()

Set qdf = db.QueryDefs("qryStlmt1901-07")

For Each varItem In Forms!StlmtSearch1.RptMth191.ItemsSelected

      strCriteria = strCriteria & ",'" & Forms!StlmtSearch1.RptMth191.ItemData(varItem) & "'"

   Next varItem

   If Len(strCriteria) = 0 Then

      MsgBox "You did not select anything from the RPT MTH list" _

             , vbExclamation, "Nothing to find!"

      Exit Sub

   End If

   strCriteria = Right(strCriteria, Len(strCriteria) - 1)

   strSQL = "SELECT * FROM SETLMT_RPT1901-1907 " & _

            "WHERE SETLMT_RPT1901-1907.RPT_MTH IN(" & strCriteria & ");"

THE ERROR HIGHLIGHTS THE BELOW LINE:

   qdf.SQL = strSQL

   DoCmd.OpenQuery "qryStlmt1901-07"

   Set db = Nothing

   Set qdf = Nothing

End Sub

Answer 3

Thanks for your suggestions.  I've tried all of the above.  Here is the truth of the matter, I'm actually coping code that works from another source; the only difference I can see is the strCriteria that is used in the code that works is "North"; "South"; etc.  My strCriteria is 1901; 1902; etc.  So I've tried putting "1901"; "1902"; etc and using strCriteria as String like the first code above.  Then I've changed to strCriteria as Integer and the list is back to 1901; 1902; etc.  Can this be the cause of the issue?

Below is the code that was copied from another source that actually works; (again the strCriteria as String in the list box is "North"; etc):

Option Compare Database

Private Sub cmdOK_Click()

 Dim db As DAO.Database

   Dim qdf As DAO.QueryDef

   Dim varItem As Variant

   Dim strCriteria As String

   Dim strSQL As String

   Set db = CurrentDb()

   Set qdf = db.QueryDefs("qryMultiSelect")

   For Each varItem In Me!lstRegions.ItemsSelected

      strCriteria = strCriteria & ",'" & Me!lstRegions.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)

   strSQL = "SELECT * FROM tblData " & _

            "WHERE tblData.Region IN(" & strCriteria & ");"

   qdf.SQL = strSQL

   DoCmd.OpenQuery "qryMultiSelect"

   Set db = Nothing

   Set qdf = Nothing

End Sub

Below is my code adapted to the most recent suggestions above; (now I'm getting a 13 error and the 3075):

Option Compare Database

Option Explicit

Private Sub cmdOK_Click()

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim varItem As Variant

Dim strCriteria As Integer

Dim strSQL As String

Set db = CurrentDb()

Set qdf = db.QueryDefs("qryStlmt1901-07")

For Each varItem In [Forms]![StlmtSearch1].[RptMth191].ItemsSelected

    If strCriteria <> "" Then

         strCriteria = strCriteria & ","

    End If

          strCriteria = strCriteria & ",'" & [Forms]![StlmtSearch1].[RptMth191].ItemData(varItem) & "'"

   Next varItem

   If Len(strCriteria) = 0 Then

      MsgBox "You did not select anything from the RPT MTH list" _

             , vbExclamation, "Nothing to find!"

      Exit Sub

   End If

   strCriteria = Right(strCriteria, Len(strCriteria) - 1)

   strSQL = "SELECT * FROM [SETLMT_RPT1901-1907] " & _

            "WHERE [SETLMT_RPT1901-1907].[RPT_MTH] IN( & strCriteria & );"

   qdf.SQL = strSQL

   DoCmd.OpenQuery "qryStlmt1901-07"

   Set db = Nothing

   Set qdf = Nothing

End Sub

Thanks again for your help.

Answer 4

You can't have a leading comma in the IN clause. So:

For Each varItem In Forms!StlmtSearch1.RptMth191.ItemsSelected

    If strCriteria <> "" Then

        strCriteria = strCriteria & ","

    End If       

    strCriteria = strCriteria & "'" & Forms!StlmtSearch1.RptMth191.ItemData(varItem) & "'"

Next varItem

Answer 5

I see a few problems:

1. You need "Option Explicit" at the top of every module. Then use vba window > tools > options and check Require Variable Declaration. This will insert that text for new modules.
2. You are using object names with funny characters. There is a reason we advise against that: they cause notational difficulties. For example when the query engine sees AAA-BBB should it conclude you mean that table name, or perhaps you want to subtract the values in two fields? So those object names need to be wrapped in square brackets.