sglee
asked on
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",
Rst.FindFirst "[Event_Name] = '" & Me!Event_Name & "'" <----- stops here
Can you help?
Thanks.
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",
Rst.FindFirst "[Event_Name] = '" & Me!Event_Name & "'" <----- stops here
Can you help?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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.
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.
ASKER
Thank you for your help!
ASKER
When I used:
Rst.FindFirst "[Event_Name] = '" & replace(Me!Event_Name, "'", '"''") & "'"
I got an error. I think quotes inside replace function is not right?
Rst.FindFirst "[Event_Name] = '" & replace(Me!Event_Name, "'", '"''") & "'"
I got an error. I think quotes inside replace function is not right?
sorry, I had reversed some quotes ...
Rst.FindFirst "[Event_Name] = '" & replace(Me!Event_Name, "'", '"''") & "'"
Rst.FindFirst "[Event_Name] = '" & replace(Me!Event_Name, "'", '"''") & "'"
ASKER
That caused an error too.
replace(Me!Event_Name, "'", '"''") ---> you are replace a single quote occurance (') with what?
replace(Me!Event_Name, "'", '"''") ---> you are replace a single quote occurance (') with what?
1 single quote with 2 single quotes...
ASKER
I magnified the text from Replace function you provided above.
Can you correct this syntax and magnify it for me?
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"
and indeed, I had a leading single quote too much
that's what I tell the "quoting hell"
ASKER
That worked. Thank you. Yes it is "quoting hell"
doublequote singlequote singlequote doublequote was very effective.
doublequote singlequote singlequote doublequote was very effective.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?