Help Syntax Error missing operator in query expression

[Comic1]

Hi,

I am getting a syntax error on a form that I am trying to make multi-select work. There is a date rage the user can choose but also if something is open or closed or both. The error is Syntax Error (missing operator) in query expression 'Vendor Hotline Log.Status = "Closed" OR Vendor Hotline Log.Status = "Open"'. It then highlights this line in the coding Apply the new SQL statement to the query
qdf.SQL = strSQL See full code below

Private Sub OK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Copy of Date Range Query")
' Loop through the selected items in the list box and build a text string
     If Me!Status1.ItemsSelected.Count > 0 Then
        For Each varItem In Me!Status1.ItemsSelected
            strCriteria = strCriteria & "Vendor Hotline Log.Status = " & Chr(34) _
                          & Me!Status1.ItemData(varItem) & Chr(34) & " OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
        strCriteria = "Vendor Hotline Log.Status Like '*'"
    End If
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM [Vendor Hotline Log] " & _
             "WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
     qdf.SQL = strSQL
     Debug.Print strSQL
' Open the query
    DoCmd.OpenQuery "Copy of Date Range Query"
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

 

[boblarson]

Because you have spaces in the table name you must use square brackets (it is good to not use spaces or special characters in field or object names):

            strCriteria = strCriteria & "[B][COLOR=red][[/COLOR][/B]Vendor Hotline Log[B][COLOR=red]][/COLOR][/B].Status = " & Chr(34) _
                          & Me!Status1.ItemData(varItem) & Chr(34) & " OR "

And get RID of these 2 lines:

    Else
        strCriteria = "Vendor Hotline Log.Status Like '*'"

because if you don't want to limit by that field, just don't include it in the Where clause.

 

[Comic1]

It's still giving me the same syntax error just with the changes made it the code

Syntax error (missing operator) in query expression "*[*Vendor Hotline Log*]*.Status = "Closed" OR *[*Vendor Hotline Log*]*.Status = "Open"

 

[boblarson]

does it have those asterisks there or is that just from the forum where you've tried to bold the parts?

 

[Comic1]

I got it now. Thank you for your hel

 

[Comic1]

What if I wanted to open in report format. How much would I need to add?

 

[boblarson]

Since you are replacing the definition of the query with the QueryDef, you can just base the report on the query and open it after you close the qdf where you currently are opening the query.

 

[Comic1]

What would the coding look like?

 

[boblarson]

What would the coding look like?

Replace this line:

' Open the query
    DoCmd.OpenQuery "Copy of Date Range Query"

with this

' Open the Report
    DoCmd.OpenReport "ReportNameHere", acViewPreview