How to preserve single quotes in a runsql update statement

[musicloverlch]

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]

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]

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]

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]

Thanks, Ron!