Quotes and Apostrophes Question

[Guest]

I am having problems with Access2000 saving a string which may include a ' or
a ". I searched the archives, and found some good information, and just
wanted to make sure I came up with the correct answer.

Please advise.

Thanks in advance.

Dim Str1 as String
Dim Str2 as String

Str1 = me.txtProbDesc
Str2 = Replace("'" & Str1 & "'", " ' ", " ' ' ")

And can it be taken a step further and run replace a second time, to handle
" as well? Such as:

Dim Str3 as String

Str3 = Replace("'" & Str2 & "'", " " ", " " " ")

Thanks again.

 

[Sylvain Lafontaine]

First, just to be sure, you must delete all the blank spaces in the call to
the replace function.

Str2 = Replace("'" & Str1 & "'", "'", "''")

Second, you don't show us how you are building your sql string; however, the
above is probably an error because you are adding and doubling the
delimiting single quotes (or apostrophes) at the same. I think that you
need is:

Str2 = Replace(Str1, "'", "''")

or maybe:

Str2 = "'" & Replace(Str1, "'", "''") & "'"

Third, you don't need to replace ' or " if you are using a parameter query
or a stored procedure or anything else that is not a « dynamically build sql
string ».

Fourth, usually, you don't have to replace both ' and ": it's either one of
these two and the one that you must replace is the one that you have used as
the string delimiter and doubling the other one will lead to error; for
example, storing O''Brien instead of O'Brien.

Of course, if you have a string delimiter embedded into another dynamically
build sql string, then you must replace these two but you usually see this
only in some complex code.