Dlookup Problem Urgent

[Guest]

Hi,

Thanks in advance.

Have a dLookup that has Criteria [Search_For]

When [Search_For] = Joes no problem works great

When [Search For] = Jo's now a problem say's syntax error. How do I get
over this.

Thanks

Trev

 

[shanesullaway via AccessMonster.com]

Hey Trever,

The single quote is what's stopping you. I believe the way to fix this is
using quotes, so it would be something like this:

[Search_For] = '" & """" & Me.dspFirstName & """" & "'"

Looks like this with spaces to help you see what's happening. (do not put
the spaces in when you are coding it!):

[Search_For] = ' " & " " " " & Me.dspFirstName & " " " " & " ' "

Hope I got this right and that this helps,
Shane

Hi,

Thanks in advance.

Have a dLookup that has Criteria [Search_For]

When [Search_For] = Joes no problem works great

When [Search For] = Jo's now a problem say's syntax error. How do I get
over this.

Thanks

Trev

 

[Guest]

Hi Trever,

Use the replace function to replace single quotes with two single quotes
like this:

replace("Jo's", "'", "''")

That's double quote, single quote, double quote, then double quote, two
single quotes, double quote.

Damian.

 

[John W. Vinson]

Hi,

Thanks in advance.

Have a dLookup that has Criteria [Search_For]

When [Search_For] = Joes no problem works great

When [Search For] = Jo's now a problem say's syntax error. How do I get
over this.

Thanks

Trev

Care to post your code? It looks like you're using ' as a delimiter in
your DLookUp... but I can't see it from here.

What's happening is that it's seeing the apostrophe in Jo's as a
closing quote mark. The getaround is to use " as a delimiter; to
include a " inside a string delimited by " use two consecutive
doublequotes:

DLookup("target", "table", "[Fieldname] = """ & [Search_For] & """")

John W. Vinson [MVP]