Access quivalent of \'

[Charles]

Hello

Small problem. I have some txt fields which can contain both some '
and some ". In MySQL, the way to neutralize a ' or a " in a query was
to place a \ before. Is there anything equivalent in Access? Or
alternatively, how should I create in VBA an sql string that says

sql="SELECT * FROM tbl WHERE tbl.name LIKE 'ooo'"

with ooo being a string, say: The'String"with'Annoying"Characters

Thanks in advance
Charles

 

[Allen Browne]

The embedded apostrophy is no problem.

The double-quote character needed to be doubled up, e.g.:
sql = Replace(sql, """", """""")

Explanation here:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Charles]

Thanks but that's not exactly solving the problem. The
The'String"with'Annoying"Characters already exists and is stored in a
database, so I am not trying to create it. I have it already stored in
a variable xxx as string, and I would like to do something like:

sql="SELECT * FROM tbl WHERE tbl.name LIKE '" & xxx & "';"

The problem is that it creates the following string:

sql="SELECT * FROM tbl WHERE tbl.name LIKE
'The'String"with'Annoying"Characters ';"

and access gets confused with the ' after "The". I know the solution
in MySql is to do a simple replace to get:

sql="SELECT * FROM tbl WHERE tbl.name LIKE 'The\'String\"with\'Annoying
\"Characters ';"

But that doesn't seem to work in Access. Any idea?

thanks
Charles

 

[Charles]

Oops. Sorry, I see what you meant. It now works.

Thanks!

 

[aaron.kempf]

SQL Server has an ESCAPE argument after the LIKE clause

This should almost suit your needs perfectly

-Aaron