DLookUp Multiple Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bumper - 21 May 2008 22:31 GMT

I know you guys have answered this a million times, but I can't figure out
where to put the apostrophe marks for the criteria used to compare text.
This is the DLookUp:

=DLookUp("Cost","qryCostbyCounty","County = " &
[Forms]![CostAnalysis]![CriminalCounty1] And "State =" &
[Forms]![CostAnalysis]![CriminalState1])

Where qryCostbyCounty.County is text, Forms.CostAnalysis.CriminalCounty1 is
text.
qryCostbyCounty.State is text and Forms.CostAnalaysis.CriminalState1 is also
text.

Help is greatly appreciated!

Thanks

Reply to this Message

Beetle - 21 May 2008 22:54 GMT

Looks like you need a few more quotes and ampersands, plus move the
"And" inside the quotes;

=DLookUp("Cost","qryCostbyCounty","County = """ &
[Forms]![CostAnalysis]![CriminalCounty1] & "And State =""" &
[Forms]![CostAnalysis]![CriminalState1] & """")

Signature

_________

Sean Bailey

> I know you guys have answered this a million times, but I can't figure out
> where to put the apostrophe marks for the criteria used to compare text.
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

Bumper - 22 May 2008 01:28 GMT

Now I get an error message, where as before it just pulled the first "Cost"
on the list. Error is #Error

> Looks like you need a few more quotes and ampersands, plus move the
> "And" inside the quotes;
[quoted text clipped - 19 lines]
> >
> > Thanks

Reply to this Message

John W. Vinson - 22 May 2008 01:48 GMT

>I know you guys have answered this a million times, but I can't figure out
>where to put the apostrophe marks for the criteria used to compare text.
[quoted text clipped - 8 lines]
>qryCostbyCounty.State is text and Forms.CostAnalaysis.CriminalState1 is also
>text.

The way I like to think about it is that the third argument to any domain
function needs to be a text string which is a valid SQL WHERE clause, without
the word WHERE. You need either ' or " marks delimiting each text criterion;
since some county names already CONTAIN ' marks, you'll want to use ".

To insert a doublequote inside a doublequote delimited string, use a double
doublequote (how's that for doubletalk!). Try

=DLookUp("Cost","qryCostbyCounty","County = """ &
[Forms]![CostAnalysis]![CriminalCounty1] & """ And State =""" &
[Forms]![CostAnalysis]![CriminalState1] & """")

This will stitch together the following pieces (converting the instances of ""
to "):

County = "
Canyon
" And State = "
Idaho
"

resulting in a valid string

County = "Canyon" and State = "Idaho"

Signature

John W. Vinson [MVP]

Reply to this Message

Bumper - 22 May 2008 02:42 GMT

My hero!! I've been working on this all day. I have so many text boxes that
are dependent on the DLookUp function, and I have only been able to get the
ones that only have one criteria to work. I knew it was the syntax involved
with the text strings, but couldn't manage to fix it. You have both given me
some very valuable information!!

Thanks!

Kim Carr

> >I know you guys have answered this a million times, but I can't figure out
> >where to put the apostrophe marks for the criteria used to compare text.
[quoted text clipped - 33 lines]
>
> County = "Canyon" and State = "Idaho"

Reply to this Message