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

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
 
G

Gina Whipp

Try:

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

HTH,
Gina Whipp
 
G

Gdog

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

Its the ' (single quote) in O'Neill thats causing the trouble.
 
G

Gina Whipp

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?
 
P

pietlinden

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 & "'"
 
G

Gina Whipp

pietlinden... learning something new here...

Are you suggesting he change peoples last names?
 
G

Gina Whipp

Sent before I finished... why not

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

then no replacing of quotation marks?
 
G

Ger

Hey

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

Ger
 
G

Gina Whipp

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
 
G

Ger

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

Thanks again.
 
Top