Link to home
Start Free TrialLog in
Avatar of printmedia
printmedia

asked on

DLOOKUP() fails when record has an apostrophe using VBA

Hi all.

I have the code below that fails whenever the value in :: has an apostrophe. How can I get this to work, not all records will have an apostrophe but sometimes it will. Thank you in advance!

If IsNull(DLookup("[Name]", "Names", "[Name] = '" & Me.Assignment_Entity & "' And [GP Name] = '" & Me.cmbGP_Name & "'")) Then

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of printmedia
printmedia

ASKER

I get the following error:

Run-time error '3075': Syntax error (missing operator) in query expression <<and then it lists the line of code with the value containing the apostrophe>>
Post the line of code exactly as it is now.

Which value has the apostrophe ?
<whenever the value in :: has an apostrophe. >
I'm confused, what field, specifically, in your expression is having the problem...?
(Not sure what  "::"  is supposed to represent...?)

But this basic syntax may work:
Replace([YourUnidentifiedField],"'","''")
Thanks DatabaseMX, I had to make the same change to another line of code that's why the error was coming up. It works great now. Thanks!
OK ... good ... because I wasn't seeing a problem.

mx