Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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.
0
sglee
Asked:
sglee
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now