Dlookup function criteria w/wo single quotes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Doug F. - 17 Sep 2007 20:17 GMT

I have Dlookup:
That WORKS:(this has single quotes around the criteria)
myRequireItemNo = DLookup("[RequireItemNo]", "V$Pamphlets", "[Pamphlet] = '"
& Forms![#10 Pamphlets]![subform #10B PamphletOrdersDetails]!Combo7 & "'") '

That FAILS:(this has no single quotes around criteria)
myRequireItemNo = DLookup("[RequireItemNo]", "V$Pamphlets", "[Pamphlet] = "
& Forms![#10 Pamphlets]![subform #10B PamphletOrdersDetails]!Combo7)

A client site runs the FAILS version ok with MS Access 2003 11.6566.8132,
when I
run it on my machine MS Access 11.5614.5606 it fails, sorry I don't have the
msg
at this moment. Thoughts?

Thanks,
Doug

Reply to this Message

Douglas J. Steele - 17 Sep 2007 22:51 GMT

Assuming Pamphlet is a text field, the quotes are necessary.

It's been that way in all versions of Access. I can't understand why it
would work on one machine and not the other, assuming you're talking about
the same database in both cases.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I have Dlookup:
> That WORKS:(this has single quotes around the criteria)
[quoted text clipped - 17 lines]
> Thanks,
> Doug

Reply to this Message

Doug F. - 18 Sep 2007 00:28 GMT

Yes, Pamphlet is a text field. Do I use single or double quotes?

When I use single quotes it works fine but I get into trouble because
one of the pamphlets has a single quote (') in its description.
I remember an example in these postings of double quote (")
and I recall """ ie 3 (") but can't find the post now.
How would the criteria look? "[Pamphlet] = """ & Forms!...?

And yes it was the same database in both cases.
With their permission I take a copy of the database from their
site to my laptop.

Thanks,
Doug

> Assuming Pamphlet is a text field, the quotes are necessary.
>
[quoted text clipped - 23 lines]
> > Thanks,
> > Doug

Reply to this Message

Douglas J. Steele - 18 Sep 2007 01:07 GMT

Either single or double quotes are okay, but both are subject to problems
under certain conditions. As you've discovered, using single quotes, you'll
run into problems if the text includes an apostrophe. However, using double
quotes, you'll run into the same problem if the text contains double quotes.

You can use

myRequireItemNo = DLookup("[RequireItemNo]", "V$Pamphlets", "[Pamphlet] = '"
& Replace(Forms![#10 Pamphlets]![subform #10B PamphletOrdersDetails]!Combo7,
"'", "''") & "'")

That replace function is Replace(<control name>, " ' ", " ' ' ")

Alternatively, you could use

myRequireItemNo = DLookup("[RequireItemNo]", "V$Pamphlets", "[Pamphlet] =
""" & Forms![#10 Pamphlets]![subform #10B PamphletOrdersDetails]!Combo7 &
"""")

(that's 3 double quotes in front of the control name, and 4 double quotes
after it). However, as mentioned, you'll run into problems if you've trying
to use something like "How to use 2" x 4" lumber" as your pamphlet name.

For more information, see my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Yes, Pamphlet is a text field. Do I use single or double quotes?
>
[quoted text clipped - 44 lines]
>> > Thanks,
>> > Doug

Reply to this Message