text string with ' included

  • Thread starter Thread starter Pat Felice
  • Start date Start date
P

Pat Felice

I know this has come up but I need to ask now

what is the proper method for including an ' within a text string

for example

Macy's

str = "select * from Adds where Jobadd ='" & ..... & "'"

currently I'm running a function to breakup the name if there is an ' in it
and
set str to "select * from Adds where jobadd like'" & Macy*s &"'"

but i'd rather do this correctly. of course the str above could return
unwanted records;

thanks
 
Use two ' characters in place of the one ' .

Easiest way to do this is to use the Replace function:

set str to "select * from Adds where jobadd like'" & Replace("Macy's", "'",
"''",,vbTextCompare) &"'"
 
I know this has come up but I need to ask now

what is the proper method for including an ' within a text string

for example

Macy's

str = "select * from Adds where Jobadd ='" & ..... & "'"

currently I'm running a function to breakup the name if there is an ' in it
and
set str to "select * from Adds where jobadd like'" & Macy*s &"'"

but i'd rather do this correctly. of course the str above could return
unwanted records;

thanks
Click to expand...

One option, if the string might contain apostrophes but will not
contain doublequotes, is to delimit the search criterion with
doublequotes instead of singlequotes. The ASCII code for " is 34, so
one way to do this is

str = "select * from Adds where Jobadd =" & Chr(34) & ..... & Chr(34)

This will give a string

SELECT * FROM Adds WHERE Jobadd = "Macy's"

which will work correctly.

If you might have both ' and " in the string (i.e. Joe's "Boom Boom
Room" Saloon) then you'll need to use Ken's solution of doubling the
'.
 
Back
Top