Syntax Error when building a filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning all,

I have a syntax error when nothing is listed in the field that starts
the filter. As long as there is a selection in the first field, all other
combinations have tested fine.

Syntax error (missing operator) in query expression '([Subject] Like * AND
[Key_word] Like '*blue*')

How I'm building the filter:
If Not IsNull(Me.subject) Then
strWhere = "[subject] = '" & Me.subject & "'"
Else
strWhere = "[subject] Like *"
End If

If Not IsNull(Me.key) Then
strWhere = strWhere & " AND [Key_word] = '" & Me.key & "'"
End If

Any suggestions?
 
Charles said:
Good morning all,

I have a syntax error when nothing is listed in the field that starts
the filter. As long as there is a selection in the first field, all other
combinations have tested fine.

Syntax error (missing operator) in query expression '([Subject] Like * AND
[Key_word] Like '*blue*')

How I'm building the filter:
If Not IsNull(Me.subject) Then
strWhere = "[subject] = '" & Me.subject & "'"
Else
strWhere = "[subject] Like *"
End If

If Not IsNull(Me.key) Then
strWhere = strWhere & " AND [Key_word] = '" & Me.key & "'"
End If

Any suggestions?
Click to expand...

Hi Charles,

I think this line of your code
strWhere = "[subject] Like *"
Click to expand...

should be

strWhere = "[subject] Like '*' "

Note the two single quotes around the *


I think it is easier to build filters like this:

'********
strWhere = ""
If Not IsNull(Me.subject) Then
strWhere = "[subject] = '" & Me.subject & "' And "
End If

If Not IsNull(Me.key) Then
strWhere = strWhere & " [Key_word] = '" & Me.key & "' And "
End If

' more IF() 's if necessary

' remove the last " And "
If Len(strWhere)>0 then
strWhere = Left(strWhere, Len(strWhere)-5)
End If
'********

Note the " And " at the end of strWhere.
That is total of 5 characters - <space>,A,n,d,<space> - that must be
removed from strWhere.


Since strWhere = "[subject] Like '*' "

is selecting ALL subjects, it is not really needed; which makes the above
code simpler.


HTH
 
Charles

Could you give an example of something you'd expect to find, using your
expression? I can't infer what you'd want to see, based on how you've
crafted your expression.

The error message suggests that you've left something out -- and I'm
wondering what the portion re: *blue* has to do with the rest.
 
Adding the single quotes did the trick, thanks!

SteveS said:
Charles said:
Good morning all,

I have a syntax error when nothing is listed in the field that starts
the filter. As long as there is a selection in the first field, all other
combinations have tested fine.

Syntax error (missing operator) in query expression '([Subject] Like * AND
[Key_word] Like '*blue*')

How I'm building the filter:
If Not IsNull(Me.subject) Then
strWhere = "[subject] = '" & Me.subject & "'"
Else
strWhere = "[subject] Like *"
End If

If Not IsNull(Me.key) Then
strWhere = strWhere & " AND [Key_word] = '" & Me.key & "'"
End If

Any suggestions?
Click to expand...

Hi Charles,

I think this line of your code
strWhere = "[subject] Like *"
Click to expand...

should be

strWhere = "[subject] Like '*' "

Note the two single quotes around the *


I think it is easier to build filters like this:

'********
strWhere = ""
If Not IsNull(Me.subject) Then
strWhere = "[subject] = '" & Me.subject & "' And "
End If

If Not IsNull(Me.key) Then
strWhere = strWhere & " [Key_word] = '" & Me.key & "' And "
End If

' more IF() 's if necessary

' remove the last " And "
If Len(strWhere)>0 then
strWhere = Left(strWhere, Len(strWhere)-5)
End If
'********

Note the " And " at the end of strWhere.
That is total of 5 characters - <space>,A,n,d,<space> - that must be
removed from strWhere.


Since strWhere = "[subject] Like '*' "

is selecting ALL subjects, it is not really needed; which makes the above
code simpler.


HTH
Click to expand...
 
Charles said:
I have a syntax error when nothing is listed in the field that starts
the filter. As long as there is a selection in the first field, all other
combinations have tested fine.

Syntax error (missing operator) in query expression '([Subject] Like * AND
[Key_word] Like '*blue*')

How I'm building the filter:
If Not IsNull(Me.subject) Then
strWhere = "[subject] = '" & Me.subject & "'"
Else
strWhere = "[subject] Like *"
End If

If Not IsNull(Me.key) Then
strWhere = strWhere & " AND [Key_word] = '" & Me.key & "'"
End If
Click to expand...


The pattern operand of the Like operator needs to be in
quotes.

But, it would be much better to omit the expression from the
where clause instead of making the query perform all the
work required to match every record. The general outline of
the code would be:

If Not IsNull(Me.subject) Then
strWhere = strWhere & " AND [subject] = '" & Me.subject &
"'"
End If
If Not IsNull(Me.key) Then
strWhere = strWhere & " AND [Key_word] = '" & Me.key & "'"
End If
. . .
strWhere = Mid(strWhere, 6)
 
Back
Top