Multiplie criteria DLookUp not working, please help

[Gina Whipp]

Hi All,

Basically, I have the below and if I run them seperately the DLookUp works
but together I get the last Purchase Order number for everything. What am I
doing wrong? SpecificationID is text and ProjectID is number.


=DLookUp("poPurchaseOrderID","qryFindPurchaseOrder","podSpecificationIDs=" &
[SpecificationID] & "" And "poProjectID='" & [txtProjectID] & "'")

Thanks.
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[Douglas J. Steele]

Not sure I understand what you mean by getting "the last Purchase Order
number for everything".

DLookup is only going to return a single value, regardless of how many
records there are that match the criteria. I'm not sure you can predict
which record is returned, even if qryFindPurchaseOrder has an Order By
clause in it.

 

[John Vinson]

Hi All,

Basically, I have the below and if I run them seperately the DLookUp works
but together I get the last Purchase Order number for everything. What am I
doing wrong? SpecificationID is text and ProjectID is number.

The third argument of any Domain function (like DLookUp or DSum)
should be a single text string which is a valid SQL WHERE clause,
without the word WHERE in it. Concatenating an empty string "" does
absolutely NOTHING - it's like adding zero to a number!

Build up the string that you want from pieces, either literal text
strings inclosed in " marks, or Form or field values. In this case,
try

=DLookUp("poPurchaseOrderID","qryFindPurchaseOrder","podSpecificationIDs="
& [SpecificationID] & " And poProjectID='" & [txtProjectID] & "'")

Assuming that you have the values 32 in SpecificationID and AX512 in
txtProjectID, this will concatenate the strings:

"podSpecificationIDs="
32
" And poProjectID='"
AX512
"'"

to get a final result

podSpecificationIDs=32 And poProjectID='AX512'

which is a valid WHERE clause, assuming that the query
qryFindPurchaseOrder contains a numeric field podSpecificationIDs and
a Text field poProjectID.


John W. Vinson[MVP]

 

[Gina Whipp]

Thanks for the replies but I had it backwards:

DLookup("poPurchaseOrderID", "qryFindPurchaseOrder",
"[podSpecificationIDs]='" & Me![txtSpecificationIDs] & "'" & " And " &
"[poProjectID]=" & Me![txtProjectID])

Quotes and apostrophes in wrong place!

Thanks anyway!
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

 

[John Vinson]

Thanks for the replies but I had it backwards:

It's not necessary to break down the strings that much. You have:

DLookup("poPurchaseOrderID", "qryFindPurchaseOrder",
"[podSpecificationIDs]='" & Me![txtSpecificationIDs] & "'" & " And " &
"[poProjectID]=" & Me![txtProjectID])

but you can also use

DLookup("poPurchaseOrderID", "qryFindPurchaseOrder",
"[podSpecificationIDs]='" & Me![txtSpecificationIDs] &
"' And [poProjectID]=" & Me![txtProjectID])

The text string

' And [poProjectID]="

can be inserted into your string piecemeal, as you're doing it; or all
as one string as above. Either will work, but the single string is
simpler.

John W. Vinson[MVP]

 

[Gina Whipp]

Thanks John

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Thanks for the replies but I had it backwards:

It's not necessary to break down the strings that much. You have:

DLookup("poPurchaseOrderID", "qryFindPurchaseOrder",
"[podSpecificationIDs]='" & Me![txtSpecificationIDs] & "'" & " And " &
"[poProjectID]=" & Me![txtProjectID])

but you can also use

DLookup("poPurchaseOrderID", "qryFindPurchaseOrder",
"[podSpecificationIDs]='" & Me![txtSpecificationIDs] &
"' And [poProjectID]=" & Me![txtProjectID])

The text string

' And [poProjectID]="

can be inserted into your string piecemeal, as you're doing it; or all
as one string as above. Either will work, but the single string is
simpler.

John W. Vinson[MVP]