Combo box syntax error

[mickeyuk]

Hi,
I have a form with a filter of 3 combo boxes, which the user select to specify the information they want to find.A report will then display all information matching the search criteria. i keep getting the error message of :

'Syntax error (missing operator)in query expression.'

Below is my code:

Private Sub CmdApplyfilter_Click()
    Dim StrCommercial As String
    Dim StrCustomer As String
    Dim StrStatus As String
    Dim StrFilter As String
    
'Code to automatically open report
    If SysCmd(acSysCmdGetObjectState, acReport, "rptRFQ Receipt to Tender Sent") <> acObjStateOpen Then
       DoCmd.OpenReport "rptRFQ Receipt to Tender Sent", acViewPreview, StrFilter
    End If
    
'Build Criteria string for Commercial Staff
    If IsNull(Me.Cbocommercial.Value) Then
        StrCommercial = "Like '*'"
    Else
        StrCommercial = "='" & Me.Cbocommercial.Value & "'"
    End If
    
    
'Build Criteria string for Customer
    If IsNull(Me.CboCustomer.Value) Then
        StrCustomer = "Like '*'"
    Else
        StrCustomer = "='" & Me.CboCustomer.Value & "'"
    End If
    
'Build Criteria string for Status
    If IsNull(Me.CboStatus.Value) Then
       StrStatus = "Like '*'"
    Else
        StrStatus = "='" & Me.CboStatus.Value & "'"
    End If

'Combine criteria strings into WHERE clause for the filter
    StrFilter = " [Commercial] " & StrCommercial & " AND [Customer]" & StrCustomer & " [Order Status] " & StrStatus
    
    
'Apply the filter and switch on
    With Reports![rptRFQ Receipt to Tender Sent]
        .Filter = StrFilter
        .FilterOn = True
    End With
End Sub

Please could anyone tell me what i've done wrong. Thanks

Michelle

 

[MrTBC]

Hi Michelle. I think it's this bit -

'Combine criteria strings into WHERE clause for the filter
StrFilter = " [Commercial] " & StrCommercial & " AND [Customer]" & StrCustomer & " [Order Status] " & StrStatus

I think you need some "=" signs in there. i.e.

StrFilter = " [Commercial] =" & StrCommercial & " AND [Customer] =" & StrCustomer & " [Order Status] =" & StrStatus

 

[TheAceMan1]

How are ya mickeyuk . . . . .

Unless its a typo, there's a missing [purple]And[/purple] in your final concatenation:

[blue]    StrFilter = " [Commercial] " & StrCommercial & " AND [Customer]" & StrCustomer & " [purple][b]And[/b][/purple] [Order Status] " & StrStatus[/blue]

See Ya! . . . . . .

 

[randy700]

Why do you have the same question posted twice? It has all ready been answered in your other post.

Randy