Apostrophies in strings

[Dymondjack]

Hello again and thakns in advance.

A theoretical question:

I've read in a few different places that depending on the usage of a string
value, occasionally the presence of an apostrophe might cause some trouble
(specifically with sql statements I believe). To make sure this won't be an
issue, I have an idea to set up two functions, one before saving the record
to replace the ' with ^^ (I use this in code templates to let me know I need
to add the variable name... it seems to have no system value when doubled
like so), and one function that will convert the ^^ back to a ' in any place
that the string value might be visible but the user. I haven't chosen the
events I want to run them on yet, but that will be a decision for a different
day. So my question is twofold...

1) Is my idea for handling the situation way off track, or is there a much
easier way to approach this that I haven't been enlightened on, and
2) Is this really any issue at all? I've never run into a case where an
apostrophe has caused error, but I would like to cover every possible error
that might come up over the course of my project.

Thanks much,
Jack

 

[Douglas J. Steele]

There's no reason to replace the value with special characters, only to have
to replace those special characters.

The issue with apostrophes occurs when you've got a SQL string along the
lines of:

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES ('" & MyValue & "')"

When MyValue contains an apostrophe, that results in something like

INSERT INTO MyTable(MyField) VALUES('O'Reilly')

When the apostrophe is encountered, it's treated as the close for the open
single quote. However, using two single quotes in a row rather than the
apostrophe will solve that problem:

INSERT INTO MyTable(MyField) VALUES('O''Reilly')

That means the original VBA should be:

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES ('" & Replace(MyValue, "'", "''") & "')"

For more information, see my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free from http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Dymondjack]

Thanks for the reply Doug

This is hardly an urgent matter at all (I haven't even started this yet, and
probably won't for a while), and I'll save your link for when I run into a
situation where I need it. In the meantime, if you could answer one more
quick question:

If the SQL with the extra single quote works if there is an aposhtrophe in
the value, will it work the same if the value doesn't contain an apostrophe?

Possibly there's an explanation for this in your link, I'll have to check it
out when I have the time. Thanks again!

 

[Douglas J. Steele]

If you look closely at the answer I suggested, you'll see it uses the
Replace function:

Replace(MyValue, "'", "''")

That changes any occurrence of ' in the text to '' (two single quotes in a
row).

If there are no occurrences of ' in the text, it doesn't do anything.