Syntax error with INSERT into command

[Leslie Isaacs]

Hello All

I have the following code which works fine until I have a value for recip
that contains an apostrophe:

DoCmd.RunSQL "INSERT into tblEMailLogs " & _
"(txtEmailFrom,txtEmailTo,dteSend,txtAttachments,intPrint) " & _
"Values (" & "'" & from & "'" & "," & "'" & recip & "'" & "," & _
Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#") & "," & "'" & _
attachments & "','1'" & ")"

When the value of recip is

John.O'(e-mail address removed)

I get Run-time error 3075
Syntax error (missing operator) in query expression "John.O'Driscoll

I can see what's causing this, but not how to fix it!

Hope someone can help.
Many thanks
Les

 

[Guest]

End If
Hi,

You can use a function to pad the quotes in any string value.

Function PadQuote(varIn As Variant) As String
If varIn Like "*'*" Then
PadQuote = Replace(varIn, "'", "''")
Else
PadQuote= varIn
End If
End Function

Thus, your SQL should look like:

"INSERT into tblEMailLogs " & _
"(txtEmailFrom,txtEmailTo,dteSend,txtAttachments,intPrint) " & _
"Values (" & "'" & from & "'" & "," & "'" & PadQuote(recip) & "'" & "," & _
Format(Now(), "\#mm\/dd\/yyyy hh\:nn\:ss\#") & "," & "'" & _
attachments & "','1'" & ")"

 

[Granny Spitz via AccessMonster.com]

Leslie Isaacs wrote:

If your last column (intPrint) really is a string value, try this:

CurrentDb.Execute "INSERT into tblEMailLogs " & _
"(txtEmailFrom,txtEmailTo,dteSend,txtAttachments,intPrint) " & _
"Values ('" & Replace(from, "'", "''") & "','" & _
Replace(recip, "'", "''") & "',#" & _
Format(Now(), "mm\/dd\/yyyy hh\:nn\:ss") & _
"#,'" & Attachments & "','1')", dbFailOnError

Otherwise, you don't need to put single quotes around the 1. And since FROM
is a reserved word, I'd advise against using it as a variable name.

 

[Ruth Isaacs]

Hello 'Granny'

Thanks for your answer.

When I tried your sugested code, I got:

Compile error - Sub or Function not defined

.... and the word "Replace" was highlighted in the code. Should I be defining
a function "Replace"?

Thanks again
Les

 

[Granny Spitz via AccessMonster.com]

When I tried your sugested code, I got:

Compile error - Sub or Function not defined

... and the word "Replace" was highlighted in the code. Should I be defining
a function "Replace"?

Sorry, hon. It means you have a version of Access older than Access 2000. I
didn't know that. Let me see if I can find some substitute code for the
Replace function.

 

[Ruth Isaacs]

Hello 'Sreedhar'

Thanks for your reply.
When I tried the code you suggested I got

Compile error - Sub or Function not defined

.... and the word "Replace" was highlighted in the code within Function
PadQuote. Should I be defining
a function "Replace"?

I wonder if this is anything to do with the fact that I am using access97?
Hope you can help.
Thanks again
Les

 

[Granny Spitz via AccessMonster.com]

You can copy the code from this web page:
http://www.mvps.org/access/strings/str0004.htm Paste it into a standard
module, then you can call the FindAndReplace function from the code I gave
you, like this:

CurrentDb.Execute "INSERT into tblEMailLogs " & _
"(txtEmailFrom,txtEmailTo,dteSend,txtAttachments,intPrint) " & _
"Values ('" & FindAndReplace(from, "'", "''") & "','" & _
FindAndReplace(recip, "'", "''") & "',#" & _
Format(Now(), "mm\/dd\/yyyy hh\:nn\:ss") & _
"#,'" & Attachments & "','1')", dbFailOnError

Save and compile.

 

[Ruth Isaacs]

'Granny Spitz '

Many thanks for that - it worked a treat!

Les

 

[Granny Spitz via AccessMonster.com]

Many thanks for that - it worked a treat!

You're welcome, Ruth. I'm so glad it's working for you!

 

[Granny Spitz via AccessMonster.com]

You're welcome, Ruth. I'm so glad it's working for you!

Er ... I mean Les! I'm so confused when you use two different names!