access query to allow apostrophes

[Johnny26652]

interesting quote :-)

[rdivilbiss]

It wasn't directed at you, it's just my favorite.  If you look around herre you'll find the French version from VGR.  I don't remember which post it is in though.

[Johnny26652]

I know its not me because i open my mouth and remove all doubt :-) I can only imagine what Mr.VGR has written.. i mean the french :-)

[rdivilbiss]

"Mieux vaut ne rien dire et passer pour un con que de l'ouvrir et ne laisser aucun doute a ce sujet"

Google translates as: “Better is worth nothing to say and pass for an idiot but to open it and not leave any doubt on this subject” which of course can't be 100% correct as it probably should translate to “Better to say nothing and pass for an idiot then to open the mouth and leave no doubt on this subject”

[VGR]

je vois qu'on s'amuse bien dans mon dos ;-)

"quand le chat n'est pas là, les souris dansent" :D

[nico5038]

Storing a variable that might contain a single quote in a SQL string is normally done by using CHR(34) like:

INSERT INTO tblX (NameField) VALUES (" & chr(34) & me!NameField & chr(34) & ");)

The chr(34) stands for a double quote and will neutralize the "query string breaking effect" that the single quote causes.

Nic;o)

[VGR]

will this work if the page's charset is not ASCII ? ;-)))

[nico5038]

Not sure what the page characterset means.
In SQL syntax the use of double quotes will neutralize single quotes like:

INSERT INTO tblX (MyName) VALUES ("O'Brian")

somewhere the SQL needs to be passed to the JetEngine, so how can a page interfere?

Nic;o)

[rdivilbiss]

will this work if the page's charset is not ASCII ? ;-)))

Unless you are using EBCDIC or a non-Latin character set.

[VGR]

Nico, in SQL double quotes do not exist. The string delimitor is the single quote. Contents should be escaped. Using char(x) is unreliable, especially for codes over 127 and reminds me of the days I programmed in BASIC back 25 years ago ;-)

[nico5038]

Hi VGR, in the Access helpfile I find:
--------------------------------------------------------------------------------------------
Include a Variable Representing Quotation Marks

You can create a string variable that represents double quotation marks, and concatenate this variable into the criteria argument along with the value of the variable. The ANSI representation for double quotation marks is Chr$(34); you could assign this value to a string variable called strQuote. You could then construct the criteria argument as in the following example:

"[LastName] = " & strQuote & strName & strQuote
--------------------------------------------------------------------------------------------

And in that way I've used it many times. As this Q has been posted in the Access area, I thought the Access rules are applicable, although I know there are different dialects :-)

Nic;o)

[Johnny26652]

Sorry Nico my bad. Actually it is Access/ASP.

[rdivilbiss]

Access SQL does vary from the SQL standard and uses double quotes.

[VGR]

expanding on Rod's comment and better explaining what I meant (all of us are right)

Storing a variable that might contain a single quote in a SQL string is normally done by using CHR(34)

What I was complaining about is the use of "SQL" in this sentence. Should read : "Access"

[rdivilbiss]

expanding on Rod's comment and better explaining what I meant (all of us are right)

What I was complaining about is the use of "SQL" in this sentence. Should read : "Access"

This is the Access Forum