Missing Operator
in [Forms]
|
From: Ann on 8 Mar 2010 12:56 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 8 Mar 2010 13:15 "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 8 Mar 2010 13:21 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 8 Mar 2010 13:32 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 8 Mar 2010 13:54
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) > |