Parsing string constant with " in sql query

[swansea68]

I have a query constructed to update to table tQA. My problem is that both the Question and Answer fields are character string. The Execute complete with no error when both Question and Answer fields conatin no '"' (double quote) character, however when any of them conatin the double quote character, I get a syntax error in query expression.

strSQLnew = "UPDATE tQA SET Question = """ & oRS![Question] & """, " & "Answer = """ _
& oRS![Answer] & """, " & "Comments = """ & oRS![Comments] & """" _
& " WHERE ID = " & oRS![ID]
CurrentProject.Connection.Execute strSQLnew

How can I avoid such error? Is there another way to construct and execute a query that allows the " as part of the value of the field?

 

[smedvid]

Check out strQuote approach in Help file... Search for "Quotation Marks in Strings"... htwh Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Aristarco]

Same error to me. Just change the " for ' (Apostrophe) or use chr$(33) instead. Have a nice day! To boldly code, where no programmer has compiled before!

 

[simma]

Aristarco,

If I change the " for ', now the query will give me a syntax error during excution when my string value contains a '.

-Swan

 

[FirstDirk]

simma,

in my programs I tried this :

Dim strSQL As String
Dim Dlm As String
Dlm = "'"
If InStr([Field2], "'&quot > 0 Then Dlm = """"
strSQL = "UPDATE tab2 SET Field2 = " & Dlm & _
[Field2] & Dlm & " where field1 = " & Dlm & [Field1] & Dlm
CurrentDb.Execute strSQL

but this still don't work if you have " and ' in the field.
Than I generate an errormassage and resume to form.

Hope it will help a little.

- FirstDirk