Form allows text entry; how to stop apostrophes from breaking SQL?

R

Rachel Garrett

I have a form with a text box (Memo data type) that allows user
comments. I also run some SQL later on that inserts these comments
elsewhere. If the user types apostrophes (like in the words don't,
let's, they're, etc.), then this will break the SQL. I would like to
write some VBA code to put escape characters into the user's comments
*before* it gets sent to SQL. Any suggestions?
 
J

John W. Vinson

I have a form with a text box (Memo data type) that allows user
comments. I also run some SQL later on that inserts these comments
elsewhere. If the user types apostrophes (like in the words don't,
let's, they're, etc.), then this will break the SQL. I would like to
write some VBA code to put escape characters into the user's comments
*before* it gets sent to SQL. Any suggestions?
Click to expand...

Well, just don't let it break the SQL. Use " to delimit the inserted string
rather than '.

Or, use

Replace(memotext, "'", "''")

to replace all instances of ' with two consecutive ', which will be parsed
back to a single apostrophe.

Perhaps you could post your current code or the SQL of the query.
 
Top