Friday, January 21, 2011

SQL failing in VBA due to apostrophes

The most common reason an SQL statement fails for when I try to run it via VBA, using DoCmd.RunSQL, is that I forget to account of apostrophes or single quotes. This is easly fixed by using the replace() function.

'Pick up the value for your variable.

strSearchTerm = Me.txtSearchBox

'Then run the variable through the replace() function, 
'replacing each single quote with two single quotes.
'This tells the parser to ignore the single quote.

strSearchTerm = Replace(strSearchTerm, " ' ", " ' ' ")

'You can also do this using the ASCII designation for a single quote, chr(39).

strSearchTerm = Replace(strSearchTerm, Chr(39), Chr(39) & Chr(39))

No comments:

Post a Comment