escaping strings in insert query

[Robbie]

I'm writing some VB code that runs an insert query. Some of the fields being
inserted comes from the current record, but as they are strings, it is
possible that they have apostrophes or even quote marks. As I am building
the query using concatenation, I was wondering if there is a built-in
function to "escape" strings like that. If not is the only other option to
create an "escape" routine myself? How does the JET engine do string
escaping (the traditional '\'???)?

just for conreteness (simplified for ease of understanding):
CurrentDb.Execute "INSERT INTO [GBH User Answers] " & _
"(PersonID, UserAnswer) " & _
"VALUES (" & PERSONID & "," & _
lblUserAnswer.Caption & ")"

surrounding the actual values with a ' works fine, until
lblUserAnswer.Caption has an '. Similar problems would occur if I surrounded
the values by CHR$(ASC(")) and the label had a " in it.

What do I do?

thanks in advance,
Robbie

 

[Allen Browne]

The convention in VBA is to double-up the quotes, e.g.:
strWhere = "(City = ""New York"") OR (City = ""Springfield"")"

Use the Replace() function to replace the one double-quote with 2
double-quote characters, and avoid the single-quote characters as a string
delimiter.

 

[Robbie]

that's sure easy. thanks for the help!

The convention in VBA is to double-up the quotes, e.g.:
strWhere = "(City = ""New York"") OR (City = ""Springfield"")"

Use the Replace() function to replace the one double-quote with 2
double-quote characters, and avoid the single-quote characters as a string
delimiter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm writing some VB code that runs an insert query. Some of the fields
being
inserted comes from the current record, but as they are strings, it is
possible that they have apostrophes or even quote marks. As I am building
the query using concatenation, I was wondering if there is a built-in
function to "escape" strings like that. If not is the only other option
to
create an "escape" routine myself? How does the JET engine do string
escaping (the traditional '\'???)?

just for conreteness (simplified for ease of understanding):
CurrentDb.Execute "INSERT INTO [GBH User Answers] " & _
"(PersonID, UserAnswer) " & _
"VALUES (" & PERSONID & "," & _
lblUserAnswer.Caption & ")"

surrounding the actual values with a ' works fine, until
lblUserAnswer.Caption has an '. Similar problems would occur if I
surrounded
the values by CHR$(ASC(")) and the label had a " in it.

What do I do?

thanks in advance,
Robbie