syntax error building filter

Topic

New Reply

I’m using 9 combo boxes on a form to build a filter for a report. The filter builds just fine if the value in cboPt is NOT LIKE (“C*”) but as soon as I change it to NOT LIKE (“C*”) AND NOT LIKE (“F*”) I get a missing operator syntax error. I’m guessing the problem is the AND but don’t know how to work around it.

The ……s below are where I’ve redacted the code.

Dim strVehNum As String
Dim strProdTypeK As String
…….
Dim strF1 As String
Dim strF8 As String
…….
Dim strFilter As String

…….
If IsNull(Me.cboPt) Then

Else
strProdTypeK = Me.cboPt
End If
strF8 = “[ProdType] ” & strProdTypeK
……
strFilter = strF1 & ” And ” & strF8

How can I use “compound” criteria in the filter statement?

Reply | Quote

Replies

In the query design window, you can specify a condition such as

Not Like “C*” And Not Like “F*”

but in the SQL string of the query this will be translated to

MyField Not Like “C*” And MyField Not Like “F*”

where MyField is the name of the field. As you can see, the field name is repeated. So you can’t simply use the text from the combo box. You’d either have to use two combo boxes, or split the value into two strings and assemble the condition the correct way. Whether you want to go that way is for you to decide.

Reply | Quote

well no wonder I kept beating my head against a wall. thanks

Reply | Quote