Error in FindFirst when the string has single quatation

Hi,

 I have an ACCESS program that generates an error when the event name contains a single quote. For example, 2013 X'mas party will cause an error where as 2013 Xmas part will not error.
 Here is the code:

    Set db = CurrentDb()
    Set Rst = db.OpenRecordset("Events", dbOpenDynaset)
    Rst.FindFirst "[Event_Name] = '" & Me!Event_Name & "'"   <----- stops here

 Can you help?

Thanks.
LVL 1
sgleeAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can do:

Rst.FindFirst "[Event_Name] = " & chr$(34) & Me!Event_Name & chr$(34)

Jim.
0
 
sgleeAuthor Commented:
Jim,
 
 That worked. Thank you.
 I know why my code caused an error (because there was a quote inside two single quotes).
 How come your code works?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's about encoding of the string delimiter.

the alternative would have been:

    Rst.FindFirst "[Event_Name] = '" & replace(Me!Event_Name, "'", '"''") & "'"

this "function" of duplicating the single quotes in the strings is what I have put into a function in all my vba projects.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sgleeAuthor Commented:
Thank you for your help!
0
 
sgleeAuthor Commented:
When I used:
 Rst.FindFirst "[Event_Name] = '" & replace(Me!Event_Name, "'", '"''") & "'"

I got an error. I think quotes inside replace function is not right?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I had reversed some quotes ...
 Rst.FindFirst "[Event_Name] = '" & replace(Me!Event_Name, "'", '"''") & "'"
0
 
sgleeAuthor Commented:
That caused an error too.
 replace(Me!Event_Name, "'", '"''")  ---> you are replace a single quote occurance (') with what?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1 single quote with 2 single quotes...
0
 
sgleeAuthor Commented:
Replace
I magnified the text from Replace function you provided above.
Can you correct this syntax and magnify it for me?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, it must be there doublequote singlequote singlequote doublequote
and indeed, I had a leading single quote too much
that's what I tell the "quoting hell"
0
 
sgleeAuthor Commented:
That worked. Thank you. Yes it is  "quoting hell"
doublequote singlequote singlequote doublequote was very effective.
0
 
Dale FyeCommented:
I use a function.
Public Function fnWrap(WrapWhat as Variant, Optional WrapWith as String = """") as Variant

fnWrap = WrapWith & Replace(WrapWhat, WrapWith, WrapWith & WrapWith) & WrapWith

End Function

Open in new window

Then I just use:

Rst.FindFirst "[Event_Name] = " & fnWrap(Me!Event_Name)

By default this wraps the text in double quotes and replaces the double quotes embedded within the WrapWhat argument with two double quotes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.