Syntax error (missing operator) in query expression (1 Viewer)

[hollering]

I'm getting the above error message when I try to open a form. The error started occurring when I tried to have two filters to control the form content. One filter is for items checked "Completed" and the other filter is for any information that was included in a Search. The actual word-for-word error message is as follows:

Syntax error (missing operator) in query expression ' AND Completed = 0'.

I even commented all my Me.Filter statements and I still got the error. Now I can't open my form. Can anyone help me? Here's a copy of my code. (minus all the stuff that absolutely doesn't apply of course)

Option Compare Database
Option Explicit

Private searchFilter As String
Const completedFilter As String = Chr(34) & "[Completed] = 0" & Chr(34)

Private Sub Form_Load()
    searchFilter = ""
    Me.Filter = completedFilter
    Me.FilterOn = True

End Sub



Private Sub goBtn_Click()

    If Not Me.FieldList_Combo.Value = "SPI#" Then
        If Not Me.FieldList_Combo.Value = "RequestDate" Then
            searchFilter = "[" & Me.FieldList_Combo.Value & "] = " & """" & Me.FieldValue_Combo.Value & """"
        Else
            searchFilter = "[RequestDate] = " & Date
        End If
    Else
        searchFilter = "[SPI#] = " & Me.FieldValue_Combo.Value
    End If
        
    Me.Filter = completedFilter
    Me.FilterOn = True

End Sub

Private Sub ShowAllTog_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Not searchFilter = "" Then
        Me.Filter = """" & searchFilter & """"
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    
    DisplayMode.Value = 2
End Sub

Private Sub ShowCurrentTog_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If searchFilter = "" Then
        Me.Filter = completedFilter
    Else
        Me.Filter = completedFilter          '& " AND " & searchFilter
    End If
    
    Me.FilterOn = True
    DisplayMode.Value = 1
End Sub

Private Sub RefreshBtn_Click()
On Error GoTo Err_RefreshBtn_Click

    If Me.DisplayMode = 1 Then
        Me.Filter = completedFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    
Exit_RefreshBtn_Click:
    Exit Sub

Err_RefreshBtn_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_RefreshBtn_Click
    
End Sub

 

[Liv Manto]

To check syntax, put this into the msgbox first before using it.

 

[MarkK]

One observation is that your constant expression...
Const completedFilter As String = Chr(34) & "[Completed] = 0" & Chr(34)
...doesn't need Chr(34) at either end. Should read...
Private Const completedFilter = "[Completed] = 0"
But this doesn't appear to be the cause of your "And Completed = 0" problem in a query. For that I'd check the record source of the form.

For toggle buttons: perhaps more appropriate to use the OnClick event rather than MouseDown.

 

[hollering]

Wow. Ever have one of those moments where you just want to kick yourself? I opened this up again this morning to look at it and there it was staring me in the face. In Form properties my filter property for some reason read " AND Completed = 0".

So since I was doing things mostly dynamically through code, I never even thought to actually check the form properties, and I'm not really sure how my property got set to show half an SQL statement anyway. Anyway, it's fixed. Thanks for your suggestions. I have taken the chr(34) and all the other extra quotes out and so far things are working well.

As for the toggle buttons, I was wondering why I used MouseDown as well, so I went back and looked...there is no OnClick event defined for toggle buttons in an option group. I wonder why that is?