Syntax Error Message 1

[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

 

[randy700]

Which line of code is highlighted when the error occurs?


Randy

 

[mickeyuk]

it isn't highlighted, when the message appears the only option it give you is OK & help button (which isn't usefull). it doesn't have a de-bug button to take you back to the code, so none of it is highlighted. I don't understand.

Michelle

 

[mickeyuk]

sorry didn't mean to post again,went back & it submitted post.

Michelle

 

[randy700]

Try putting a break point (F9) at the beginning of your code and stepping through it (F5). That will tell you which line is causing the error message to appear.


Randy

 

[MoLaker]

Your code checks for null, but does not check for "".

But, your immediate problem is a missing AND.

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

 

[PHV]

And lack of space here:
" AND [Customer][highlight] [/highlight]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

PHV . . . . .

That got me at first too. But if you look at it, [blue]the spaces are filled in with the final concatenation. . .[/blue]

See Ya! . . . . . .

 

[PHV]

I pointed out the final concatenation, ie the building of StrFilter that could contain AND [Customer]Like

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

PHV . . . . .

[blue]Saw that too![/blue]

See Ya! . . . . . .

 

[mickeyuk]

Thanks for all your suggestions. I've got it working, it was the AND. I have also changed the spacing. I've checked it sooo many times can't believe I missed it! Thanks anyway.

Michelle

 

[mickeyuk]

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=red]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=red]'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=red]& " 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

 

[MoLaker]

Print out your filter string in the debug window so you can see exactly what is being applied.

'Apply the filter and switch on
    With Reports![rptRFQ Receipt to Tender Sent]
        .Filter = StrFilter
        .FilterOn = True
[red]debug.print StrFilter[/red]
    End With

Then view the debug or intermediate window while viewing form or report code. You may still find a syntax error or you may see why all records aren't being returned.

 

[TheAceMan1]

mickeyuk . . . . .

The best way to [blue]test/verify[/blue] a problem like this is to [purple]make a test query[/purple], [blue]directly plugging in the criteria[/blue] and viewing the returned recordset. This will qualify if you right or wrong in whats expected . . . .

See Ya! . . . . . .

 

[mickeyuk]

Hi,

Thanks for all your help! It came up with no errors. I think its something to do with the business development combo box as when i taken it away it works fine. i've also added a different combo box & that worked fine too.

Michelle

 

[mickeyuk]

Hi all,

Have been trying to figure out whats going worng with the database. still think its something to do with the business development staff. i dont know if there is something in VB code that doesn't like any of the words?? i've tried using various combinations e.g. 'businss', 'business development'. any ideas? when i debug it no errors appear.

Thanks

Michelle

 

[randy700]

Run it again, with the Debug.Print strFilter command suggested by MoLaker, then copy/paste the string that prints in the debug window.


Randy