Combo Box Syntax Error

[mickey_lin_uk]

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

 

[ColinEssex]

which bit is highlighted as being the error?

Col

 

[mickey_lin_uk]

none of it is. a message box appears to say there's an error & there is only OK or Help buttons(which don't tell you anything). it doens't let you de-bug it.

 

[RoyVidar]

Shouldn't there be some operator in the criteria?

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

Edit: Now see they are incorporated in assigning the individual criterion.

Next quess, would be that the bound column of the combos wasn't text, but numeric, or that for some reason the combo when no selection returned an empty string (""), in stead of Null, which can be tested with for instance:

if trim$(me!CboStatus.value & "")="" then ' Null or ""

- I'l guess I'll have to learn to read the whole post first

 

[RoyVidar]

- oh - and a missing AND before the orderthingie...

 

[mickey_lin_uk]

Thanks, it was the AND. I checked it so many times, can't believe I missed it!

Michelle

 

[mickey_lin_uk]

Hi all,

Have run into difficulty again, I have added another combo box 'Business Development',when i run my form now it doesn't display all the information that matches the search criteria.I know there are more records that should match the criteria, i don't understand why it brings up some records and not others?!

below is my code the bits in red are the new parts.

Private Sub CmdApplyfilter_Click()
    Dim StrCommercial As String
    Dim StrCustomer As String
    Dim StrStatus As String
    [COLOR=DarkRed]Dim Strbusinessdevelopment As String [/COLOR] 
    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

[COLOR=DarkRed]'Build Criteria string for business development staff
    If IsNull(Me.Cbobusinessdevelopment.Value) Then
        Strbusinessdevelopment = "Like '*'"
    Else
        Strbusinessdevelopment = "='" & Me.Cbobusinessdevelopment.Value & "'"
    End If
    [/COLOR] 
'Combine criteria strings into WHERE clause for the filter
    StrFilter = " [Commercial] " & StrCommercial & " AND [Customer]" & StrCustomer & " AND [Order Status] " & StrStatus [COLOR=DarkRed]& " AND [Business Development Staff] " & Strbusinessdevelopment[/COLOR]
    
'Apply the filter and switch on
    With Reports![rptRFQ Receipt to Tender Sent]
        .Filter = StrFilter
        .FilterOn = True
    End With
End Sub

Thanks in advance

Michelle