RunSql with " ' " in it...

  • Thread starter Thread starter Alu_GK
  • Start date Start date
A

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
 
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 & """"
 
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
 
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
 
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)


Alu_GK said:
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
Click to expand...
 
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.
 
Back
Top