RunSql with " ' " in it...

[Alu_GK]

Hello -
I'm Trying to run an sql statement that I'm creating in the VB code.
DoCmd.RunSQL sSQLforUpdate

The code is creating an update query ("sSQLforUpdate") that update table 1
from the current form (that bounded to table 2).
It is working ok, up until there is a special char such as " ' " in the
string that need to be updated (sSQLforUpdate).
For example:
The word "Hello" will be update ok, but the word "Hell'o" will create an
error that wouldn't let me run the query.
I've by pass it be using a "remove_Char" function, but I need to find a way
to update information that includes also this " ' " sign.
Do you know how do I need to write the sql in order to over come this problem?

Thank you

 

[Klatuu]

It is a matter of correctly construction your SQL string.

If you use single quotes as a delimiter and the value you include in the
string has a single qoute, you will get the error. For exampe, assume the
control txtWorld = Hell'o

This will cause the error:
strSQL = "UPDATE SomeTable SET MyField = '" & Me.txtWorld & "'"

However, if you use two double quotes where the single qoutes are in your
assignment, it will correctly delimit the string:

strSQL = "UPDATE SomeTable SET MyField = """ & Me.txtWorld & """"

 

[Douglas J. Steele]

Of course, if Me.txtWorld contains a double quote (Dave's "Access Shack"),
using double quotes as a delimiter won't work either.

I talked about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Alu_GK]

Hello -
Thanks, both answers where helpful.
I think the best way is to chech whether i have a " ' " or " " " in my
string and to run the correct sql for the string special chars.
Thank you

 

[Douglas J. Steele]

So what do you consider "the correct sql" when you have both single quotes
and double quotes in your string?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello -
Thanks, both answers where helpful.
I think the best way is to chech whether i have a " ' " or " " " in my
string and to run the correct sql for the string special chars.
Thank you

 

[Alu_GK]

Hello -
Your comment was considered after our discussion here, and eventually I've
decided to run the sql considering only situation with " ' ".
The way I handle the " " " is that I've sent the string to a "Replace"
function to remove the (") if there's any, and remove the option of
appearance of " " " in the string, and also the need for 2 sql lines, and
especially the bug that would happened if what you suggested will occur.
Thanks very much!
Have a good weekend.