handling single quotes in an sql statement

[Kameron]

Hello everyone -

I am tring to run an insert statement but there are cases
where a text or memo field stores single quotes so, the
statement crashes. I have already tried to use the
replace function to replace single with double quotes but
then I run into problems when I have a double quote.
What is the correct way to handle single quotes? Thanks
in advance!

 

[fredg]

use chr(34) & [FieldName] & chr(34)

Dim strSQL as String
strSQL = "INSERT INTO YourTable(FieldName) SELECT " & Chr(34) &
[FieldName] & Chr(34) & ";"
CurrentDb.Execute strSQL, dbFailOnError

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Replace() function like this:

strSQL = UPDATE TableName SET MemoColumn = "

' replace single quotes with 2 single quotes
strMemo = Replace(txtMemo, "'", "''")

strSQL = strSQL & "'" & strMemo & "'"

Do the same for double quotes (") - replace w/ 2 double quotes. An
easy way to do this is to have a constant for one double qoute:

Const Q = """" ' Chr(34) - evaluates to "

strMemo = Replace(txtMemo, Q, Q & Q)

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBKyB4echKqOuFEgEQIPNACg8Sz0pKBd8mfF1r+AXC2eGSRLnQYAoNZ7
aHh1iu+9BB8MvOkUT/pzpadq
=LBey
-----END PGP SIGNATURE-----