Apostrophe Problem

[Anthony]

What do we do if we want to print a company name on our reports, use the
company variable (cName) in an sql statemnt, if that company name has an
apostrophe?

 

[Douglas J. Steele]

Having an apostrophe in the name shouldn't affect printing it.

In your SQL, it depends on what you're using as a delimiter.

If you're using double quotes as a delimiter, you should be fine:

SELECT * FROM MyTable WHERE Surname = "O'Reilly"

Writing that in VBA, you'd have something like:

strSQL = "SELECT * FROM MyTable " & _
"WHERE Surname = """ & strName & """"

If you're using single quotes as a delimiter, you need to double up on the
apostrophe.

SELECT * FROM MyTable WHERE Surname = 'O''Reilly'

(exagerated for clarity, that's SELECT * FROM MyTable WHERE Surname = ' O '
' Reilly ' )

Writing that in VBA, you'd have something like:

strSQL = "SELECT * FROM MyTable " & _
"WHERE Surname = '" & Replace(strName, "'", "''") & "'"

again, exagerated for clarity, that's

strSQL = "SELECT * FROM MyTable " & _
"WHERE Surname = ' " & Replace(strName, " ' ", " ' ' ") & " ' "


You might find it useful to read my May, 2004 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html