Missing Operator

  • Thread starter Thread starter Ann
  • Start date Start date
A

Ann

I am using Access 2002. I have a listbox with two columns that allows me to
choose one or more items, then generates a report for those I've chosen. I
am receiving an error on some of them. It is:

Syntax error (missing operator) in query expression '([txtCourseTitle] =
'Career Edge Director's Program' And [txtVendorName] = 'Career Edge Right
Management')'.

I thought it was the apostrophe but I have others with apostrophes and they
work fine. I have some with double quotes and those are good too so I'm kind
of at a loss for what the problem is. Can anyone help me out? Thanks in
advance.
 
Ann said:
I am using Access 2002. I have a listbox with two columns that allows me
to
choose one or more items, then generates a report for those I've chosen.
I
am receiving an error on some of them. It is:

Syntax error (missing operator) in query expression '([txtCourseTitle] =
'Career Edge Director's Program' And [txtVendorName] = 'Career Edge Right
Management')'.

I thought it was the apostrophe but I have others with apostrophes and
they
work fine. I have some with double quotes and those are good too so I'm
kind
of at a loss for what the problem is. Can anyone help me out? Thanks in
advance.
Click to expand...


It is almost certainly the apostrophe in "Career Edge Director's Program".
The best way to solve this problem is to change the code that builds the
criteria argument so that it uses double-quotes around the sting literals
instead of apostrophe's/single-quotes. You didn't post the code, but the
end result should be like this:

([txtCourseTitle] = "Career Edge Director's Program" And [txtVendorName]
= "Career Edge Right Management")

You may currently have code along these lines:

stCriteria = _
"([txtCourseTitle] = '" & Me.txtCourseTitle & _
"' And [txtVendorName] = '" & Me.txtVendorName & "')"

If so, you could change it like this:

stCriteria = _
"([txtCourseTitle] = """ & Me.txtCourseTitle & _
""" And [txtVendorName] = """ & Me.txtVendorName & """)"

The double-quotes that are to be part of the criteria string are doubled-up
so that they will be recognized inside the double-quotes that delimit the
string literals.
 
I definitely think it's the apostrophe. Can't you take it out of the value?
It should only take one second to verify that's the problem.

If this works somewhere else, is it possible the other places didn't use
apostrophes to delimit the string itself?

(Note: I'm just assuming the outer apostrophes are correct as I have no idea
how or where this is being used.)
 
Ann said:
I am using Access 2002. I have a listbox with two columns that allows me to
choose one or more items, then generates a report for those I've chosen. I
am receiving an error on some of them. It is:

Syntax error (missing operator) in query expression '([txtCourseTitle] =
'Career Edge Director's Program' And [txtVendorName] = 'Career Edge Right
Management')'.

I thought it was the apostrophe but I have others with apostrophes and they
work fine. I have some with double quotes and those are good too so I'm kind
of at a loss for what the problem is.
Click to expand...

There is a missing ' it should be
'Career Edge Right Management' )
But, if that's the problem, I don't see how some of them
would work so I assume you introduced a typo when retyping
the error message.

It might help if you posted a Copy/Paste of the query's SQL
view (or the code that creates it).
 
Thank you that worked great. I had Chr(39) in the code but changed it to the
double quotes you suggested and everything worked.

Dirk Goldgar said:
Ann said:
I am using Access 2002. I have a listbox with two columns that allows me
to
choose one or more items, then generates a report for those I've chosen.
I
am receiving an error on some of them. It is:

Syntax error (missing operator) in query expression '([txtCourseTitle] =
'Career Edge Director's Program' And [txtVendorName] = 'Career Edge Right
Management')'.

I thought it was the apostrophe but I have others with apostrophes and
they
work fine. I have some with double quotes and those are good too so I'm
kind
of at a loss for what the problem is. Can anyone help me out? Thanks in
advance.
Click to expand...


It is almost certainly the apostrophe in "Career Edge Director's Program".
The best way to solve this problem is to change the code that builds the
criteria argument so that it uses double-quotes around the sting literals
instead of apostrophe's/single-quotes. You didn't post the code, but the
end result should be like this:

([txtCourseTitle] = "Career Edge Director's Program" And [txtVendorName]
= "Career Edge Right Management")

You may currently have code along these lines:

stCriteria = _
"([txtCourseTitle] = '" & Me.txtCourseTitle & _
"' And [txtVendorName] = '" & Me.txtVendorName & "')"

If so, you could change it like this:

stCriteria = _
"([txtCourseTitle] = """ & Me.txtCourseTitle & _
""" And [txtVendorName] = """ & Me.txtVendorName & """)"

The double-quotes that are to be part of the criteria string are doubled-up
so that they will be recognized inside the double-quotes that delimit the
string literals.


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Ann said:
Thank you that worked great. I had Chr(39) in the code but changed it to
the
double quotes you suggested and everything worked.
Click to expand...

You can use Chr(34) to avoid having to double up the quotes:

stCriteria = _
"([txtCourseTitle] = " & _
Chr(34) & Me.txtCourseTitle & Chr(34) & _
" And [txtVendorName] = " & _
Chr(34) & Me.txtVendorName & Chr(34) & ")"

Of course, you do still have a risk if the values you're embedding have
double-quotes in them. But that's much less likely than
single-quotes/apostrophes, and you probably know if you need to allow for
that. If you do, you have to do something like this:

Const Q As String = """"
Const QQ As String = Q & Q

stCriteria = _
"([txtCourseTitle] = " & _
Q & Replace(Me.txtCourseTitle, Q, QQ) & Q & _
" And [txtVendorName] = " & _
Q & Replace(Me.txtVendorName, Q, QQ) & Q & ")"
 
Back
Top