From Newsgroup: comp.databases.ms-access

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
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-access

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" & "''" & ">"
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-access

musicloverlch laid this down on his screen :

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

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

--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-access

On Friday, December 18, 2020 at 10:22:25 AM UTC-6, musicloverlch wrote:

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

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.
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ms-access

Thanks, Ron!


On Monday, December 21, 2020 at 9:20:24 AM UTC-6, Ron Paii wrote:

On Friday, December 18, 2020 at 10:22:25 AM UTC-6, musicloverlch wrote:

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

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.

--- Synchronet 3.21b-Linux NewsLink 1.2