Missing Operator

[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.

 

[Dirk Goldgar]

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.

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.

 

[Jonathan Wood]

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.)

 

[Marshall Barton]

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.

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).

 

[Ann]

Thank you that worked great. I had Chr(39) in the code but changed it to the
double quotes you suggested and everything worked.

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.

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)

 

[Dirk Goldgar]

Thank you that worked great. I had Chr(39) in the code but changed it to
the
double quotes you suggested and everything worked.

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 & ")"