Hi,
With Jet, double the delimiter when the delimiter appears in the string:
SELECT whaterver FROM somewhere WHERE height ="5'-11 7/8"""
Indeed, we want 5'-11 7/8"
and we will use " as delimiter, so, since " already appear , double it:
5'-11 7/8""
then, add the delimiters
"5'-11 7/8"""
or, if you build the sting at runtime:
str= "SELECT whatever FROM somewhere WHERE height=""" & replace(
suppliedByEndUser, """", """""" ) & """"
Again, the same logic is applied. We want, as third augment of replace, the
result "" ( two double quotes). Since we plan to use it as delimiter (we
have no choice in VBA), we first have to double it: """" and then,
add the delimiter at each ends, to get a sequence of six double quotes.
Same thing for the wanted:
SELECT whatever FROM somewhere FROM height="
and for the last part, and also as the second argument of replace, where we
wanted just one double quote.
Hoping it may help,
Vanderghast, Access MVP
Albert said:
If string in SQL contains both single and double quote and I don't want to
replace with other characters, Still have any other solutions ?
