From: Ann on
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.
From: Dirk Goldgar on
"Ann" <Ann(a)discussions.microsoft.com> wrote in message
news:174121BD-FB93-41EA-816F-6CF8FB3DA20A(a)microsoft.com...
>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)

From: Jonathan Wood on
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.)

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"Ann" <Ann(a)discussions.microsoft.com> wrote in message
news:174121BD-FB93-41EA-816F-6CF8FB3DA20A(a)microsoft.com...
> 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.

From: Marshall Barton on
Ann wrote:

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

--
Marsh
MVP [MS Access]
From: Ann on
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" wrote:

> "Ann" <Ann(a)discussions.microsoft.com> wrote in message
> news:174121BD-FB93-41EA-816F-6CF8FB3DA20A(a)microsoft.com...
> >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)
>