SQL Problem

[Reg Booth]

I am having a problem writing an SQL update. I am updating a table
(table2) with a text string from table1. It works fine until there is an
apostrophe or quotation marks embedded in the text string that I am
using to update the field.

See below for a code snippet of my SQL code

dim rs as recordset
set rs = db.openrecordset("table1")

While not rs.eof

sqlStr = "UPDATE table2 Set field1 = '" & rs!Field1 & "'"
db.execute sqlstr, DB_FAILONERROR

Wend

Thanks in advance for your assistance.

Reg

 

[Van T. Dinh]

Use the Replace() function to replace the single quote with 2 single quotes
like:

sqlStr = "UPDATE table2 Set field1 = '" & Replace(rs!Field1, "'", "''") &
"'"

The 2nd and 3rd arguments of the Replace funtion above are:
(Note: DQ = Double Quote, SQ = Single Quote)

2nd argument: DQ + SQ + DQ
3rd argument: DQ + SQ + SQ + DQ

 

[tw]

that's because it is stopping your string field at the ' single quote in the
data. It's best to restrict data entry to exclude special characters that
are needed in code. See if you can reverse your sql quote marks to ... I'm
not an expert with Access so I'm not sure if it will work using single
quotes around your main statement and double quotes around you data field
data. But try it...

sqlStr = 'UPDATE table2 Set field1 = "' & rs!Field1 & '"'
db.execute sqlstr, DB_FAILONERROR