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
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Doug F. said:
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
Douglas J. Steele said:
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.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Doug F. said:
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
