need to do a findfirst with a string that has an ' in it

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My code is crashing on the following line

rs.FindFirst "LastName='" & Ary(4) & "'"

I get an syntax error, missing operator in expression - error.

Turns out the value is O'brien, It treats ' as the end of the string and
says the rest of it is garbage. Is there a way to escape it with the replace
function.

Thanks for any help.

-- 'If it looks like your going to bite it, try not to ruin the shoot' -
stuntman credo
 
Hi.
Is there a way to escape it with the replace
function.
Click to expand...

No. Whatever you replace in the string will be compared with the string
value in the table. Unless you would rather store O'Brien as O>Brian or
substitute some other symbol for the apostrophe, this isn't a recommended
solution. One recommended solution would be to use a different string
delimiter, the pair of double quotes. Try:

rs.FindFirst "LastName = """ & Ary(4) & """"


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
Turns out the value is O'brien, It treats ' as the end of the string
and says the rest of it is garbage. Is there a way to escape it with
the replace function.
Click to expand...

Like Gunny says, you have to double any quote marks inside a string
delimited by the same quotes. I you wanted the string

My Name is "O'Brien"!

then you could use

strTemp = "My Name is ""O'Brien""!"

or else

WHERE Claim = 'My Name is "O''Brien"!'

if you see what I mean. The easiest answer is to use the Replace function
to double up whatever you want to do:

strTemp = "Last Name = """ & Replace(ary(4), """", """""") & """"


(just count all those double quotes!!)

Hope that helps


Tim F
 
Back
Top