Apostrophe Problem

  • Thread starter Thread starter Anthony
  • Start date Start date
A

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?
 
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
 
Back
Top