Single quote in query string causing problems

[INeedAccessHelp]

Here is the code:

CurrentDb.Execute "UPDATE table SET table.COMMENTS =" & "'" & Me![ReqComments] & "'" & " WHERE table.ID = 2;"

The problem comes about when the Me![ReqComments] memo field contains a single quote (example: John's book).

Any help with this would be greatly appreciated. Thanks in advance.

--Rob

 

[LNBruno]

Wrap the value in a pair of double quotes:

& """" & Me![ReqComments] & """"

< M!ke >

 

[Tranman]

Rob,
You could use the Replace function:

"UPDATE table SET table.COMMENTS =" & "'" & Replace(Me![ReqComments],"'","") & "'" & " WHERE table.ID = 2;"

You might have to do it in a string outside the SQL if MS has trouble parsing the statement.

Tranman

 

[INeedAccessHelp]

Thanks Mike! That solves the problem if the user enters a comment that includes a single quote.

Now, if a user enters a comment with a double quote, I get an error.

How can I allow BOTH of these characters?

 

[mndrlion]

If you use single quotes as the text delimiter, use the following:

    Dim strSQL  As String
    Dim strComment As String
    
    strComment = Replace(txtComment, "'", "''")
     
    strSQL = "Update tblComments Set Comment = '" & strComment & "' WHERE key = 'a';"
    
    CurrentDb.Execute strSQL

If you use double quotes as the text delimiter, use the following:

    Dim strSQL  As String
    Dim strComment As String
    
    strComment = Replace(txtComment, """", """""")
     
    strSQL = "Update tblComments Set Comment = """ & strComment & """ WHERE key = 'a';"
    
    CurrentDb.Execute strSQL

 

[oharab]

In thread705-653379 I posted a function called Jetstream that copes with " and ' embedded in your SQL string.

Copy it into a module and change your SQL to

CurrentDb.Execute "UPDATE table SET table.COMMENTS =" & JetStream(Me![ReqComments]) & " WHERE table.ID = 2;"

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;

NoFX-The Decline
----------------------------------------------

 

[INeedAccessHelp]

Thanks Ben. Works great!