sql query where search criteria contains a single quote e.g O'Neill

  • Thread starter Thread starter Gerard.Clynch
  • Start date Start date
G

Gerard.Clynch

Hi

I'm having trouble with the following sql query. Any ideas?

SELECT * FROM SALES WHERE ACCOUNT = "O'NEILL"

The error is:

syntax error: invalid filter in Where clause.

Thanks
 
Try:

SELECT Sales.*, Sales.Account
FROM Sales
WHERE (((Sales.Account)="O'NEILL"));

HTH,
Gina Whipp
 
No Joy there I'm afraid Gina but thanks for the help anyway.

Its the ' (single quote) in O'Neill thats causing the trouble.
 
Not sure it's that, I have a query:

SELECT tblActivities.*, tblActivities.aKeyword
FROM tblActivities
WHERE (((tblActivities.aKeyword)="EDI's"));

And it runs just fine. What happens when you run the query, so you get the
same error message or something new?
 
Gerard, where are you running this? In code or in a query? If you're
doing it in code, then a single quote is a text delimiter, so you have
to double it (you can use Replace).

eg.

strLastName=Replace(strLastname,"'","'') that's replace ..., single
quote, double single quote)
strSQL = "SELECT * FROM SALES WHERE Account=' " & strLastName & "'"
 
pietlinden... learning something new here...

Are you suggesting he change peoples last names?
 
Sent before I finished... why not

strSQL = "SELECT * FROM SALES WHERE ACCOUNT = ""O'NEILL"""

then no replacing of quotation marks?
 
Hey

None of this is working. I should have mentioned that its a linked
Database using an ODBC connection.

Ger
 
Yeppers, you probably should have mentioned that as I do not have a link via
ODBC and I don't know whether or not that makes a difference in the way it's
done. NOW my suggestion would be to repost the question mentioning that
fact and see if someone else can help OR what a day or two and see if one of
the gurus can help.

Sorry I couldn't come up with a solution... 8-(

Gina Whipp
 
Thanks for your help anyway Gina.
I'll prob post it up again in a while mentioning the ODBC link.

Thanks again.
 
Back
Top