Single quotes in Where clauses

[Guest]

I am creating a database for a small aviation research library. If a book
title, author, or publisher contains a single quotation mark such as the
following examples -- Steve’s Structural Designs, John O’Malley, or Bob’s
Publishing Company then where clauses used in searches fail. For example,
Where LibCat.AuthorLastName = ‘O’Malley’ ends up with three single
quotation marks, one too many, and an error is generated during code
execution.

Replacing the offending single quotation marks in where clause strings with
their equivalents, Chr(39) does not solve the problem.

Also, replacing single quotation marks in the database tables with the
“backward single quotation markâ€, i.e. the ` character also does not solve
the problem.

Thanks, for any help

 

[Allen Browne]

Use double-quotes instead of single quotes in your strings.

For example:
DLookup("ClientID", "tblClient", "[Surname] = ""O'Malley""")

You cannot write:
"This "word" is in quotes" '<=Error!
because VBA thinks the string ends just before Word, and can't figure out
what to do with the rest of the line. So, the convention is to to double-up
the quotes if they are embedded, i.e.:
"This ""word"" is in quotes"