DLookUp Multiple Criteria

[Bumper]

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

 

[Beetle]

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] & """")

 

[Bumper]

Now I get an error message, where as before it just pulled the first "Cost"

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] & """")

--
_________

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.
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

 

[John W. Vinson]

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.

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"

 

[Bumper]

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.
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.

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"