How to preserve single quotes in a runsql update statement

35 views
Skip to first unread message

musicloverlch

unread,
Dec 18, 2020, 11:22:25 AM12/18/20
to
I have a line in an email text that has single quotes in it like this:

EmailText = "This is an 'example' of the text."

Then when I run this:

DoCmd.RunSQL "UPDATE tblEmailings SET tblEmailings.Body = " & EmailText & ";"

It errors out and I'm sure it's because of the single quotes in the EmailText.

How do I get around this? A web search has proved to be unhelpful.

Thanks,
Laura

musicloverlch

unread,
Dec 18, 2020, 12:19:20 PM12/18/20
to
I'm actually writing HTML and I figured it out, which always seems to happen right after I ask here. I have updated the SQL statement to be like this for quotes:

EmailText = "<p style=''" & "font-family:calibri" & "''" & ">"

Ron Weiner

unread,
Dec 18, 2020, 7:15:07 PM12/18/20
to
musicloverlch laid this down on his screen :
You can use the replace command to "Double up" single quotes like this:

DoCmd.RunSQL "UPDATE tblEmailings SET tblEmailings.Body = " & Replace
(EmailText,"'","''") & ";"

It is good practice to use the replace command on any string variable
that is being used inside Append / Update query. Especially if you are
updating surnames. It defends against what I have called the Irish /
Italian problem. It only takes a single O'Malley, or D'Angelo in your
data to ruin what would have been a perfectly good day.

Rdub

--
This email has been checked for viruses by AVG.
https://www.avg.com

Ron Paii

unread,
Dec 21, 2020, 10:20:24 AM12/21/20
to
Replace will work for the 1 character, but others can cause problems.
if the text is no more then 255 characters use a parameter query, which will allow any text.

Dim updateBody As DAO.QueryDef
Set updateBody = CurrentDb.CreateQueryDef(vbNullString, "PARAMETERS [newBody] Text ( 255 ); UPDATE tblEmailings SET tblEmailings.Body = [newBody];")
updateBody.Parameters(0) = "This is an 'example' of the text."
updateBody.Execute
Set updateBody = Nothing

If it is a memo, a recordset may work.

musicloverlch

unread,
Jan 20, 2021, 2:21:50 PMJan 20
to
Thanks, Ron!
Reply all
Reply to author
Forward
0 new messages