problem with text delimiters

[SunWuKung]

I am creating an SQLstring that would insert a text into a table. My problem is that the text which will be inserted includes the ' character so when I execute it I get an error message. (In my case Me.StoredString contains an ' character.)

This is the syntax I use now - how should I be doing this?


" INSERT INTO ..... VALUES ( ' " & Me.StoredString & " ' )"

Thanks for the help.
SWK

 

[AncientOne]

replace the ' character in your string with its ASCII code, which is Chr$(39):

Thus, "Peter's Pies" becomes "Peter" & CHR$(39)& " s Pies"

 

[jaydwest]

working with ' in SQL Strings

We have found the easiest way to resolve this problem is to create a global string, we name it DQ (for double quote) and define it as follows.

Public DQ as string

DQ = chr$(34)

Then you can surround your text field with DQs as shown below

WHERE (xxxxxxx = " & DQ & [Field Name] & DQ & ")

Please let me know if you have any questions or if you have any problems. I think this is easier that changing the astrophe because you donn't have to parse the field.

Good luck.

 

[SunWuKung]

That is very helpful.
Many thanks to both of you.
I didn't go for the CHR$(39) solution because of the need for parsing the field, so I chickened out and used rst.AddNew instead of insert.
But I will try this DQ method. It looks very nice.

Thanks again.

SWK

 

[Estuardo]

Good Evening,

I just want to appoint that the following code, does not work:

Quotes = chr$(34)

because the Editor does check the sintax at run time and that is a clear sintax error. It is equals to

Quotes = "

and it can be assigned. One to do it is

Quotes = """"

and it will works as espected.


Best Regards

Estuardo

 

[AncientOne]

A routine for handling double quotes, given all conditions is contained in the Access97 Developer's Handbook by Litwin, Getz, Gilbert, Chapter 6.

My original adice was given on the assumption that parsing the field was the appropriate response to a one-off occurrence.