DLookup with multiple criteria

[Nadine67]

Hello and thank you in advance for any attention this post may receive.

Table = tblUnit
Result table column = "Make"
Criteria table columns = "ID_Unit" and "Date_Commissioned"
Criteria = ID_Unit = txtUnit value and Max(Date_Commissioned)

My desired result is to return (in txtMake) the Make of the unit in txtUnit, that has the latest commissioned date from tblUnit.
There are multiples of the same unit number in ID_Unit with various commission dates.

This is my txtMake Control Source:
=IIf(IsNull([txtUnit]),"",DLookUp("Make","tblUnit","([ID_Unit]=" & [txtUnit] And [Date_Commissioned]=" & Max([Date_Commissioned]))"))

It is returning the first occurrence where [ID_Unit] = [txtUnit] only.

Thank you again!

 

[stumac]

Does using =Max([Date_Commissioned]) as the control source for a text box give you the maximum date you expect to use for the lookup?

If so then try:

=IIf(IsNull([txtUnit]),"",DLookUp("Make","tblUnit","([ID_Unit]=" & [txtUnit] And [Date_Commissioned]=#" & Max([Date_Commissioned])"#))"

if you are looking to use the latest date from the table then you would need to combine the Dlookup with a Dmax.

 

[welshgasman]

That would probably be as you have not concatenated the remaining criteria I believe?
Should be more like

=IIf(IsNull([txtUnit]),"",DLookUp("Make","tblUnit","[ID_Unit]=" & [txtUnit] & " And [Date_Commissioned]=#" & Max([Date_Commissioned]) & "#""""))

Not too sure how many double quotes should be at the end, so try permutations. :D

HTH

 

[xenou]

Nesting a DMax in a DLookup is also a way to go:

=IIf(IsNull([txtUnit]),"",DLookUp("Make","tblUnit","([ID_Unit]=" & [txtUnit] & " And [Date_Commissioned]=" & "#" & DMax([Date_Commissioned], [tblUnit], "ID_Unit=" & [txtUnit]) & "#" & ")"))

The above is long and ugly enough that I'd be searching for a way to decompose it (possibly create a custom function to handle the logic). Also you can put the calculation of Date_Commissioned into its own textbox for easier testing and writing of the function. Later (when all is working) then make that textbox for Date_Commissioned invisible.

'//Place in a hidden (not visible field) called txtMaxDateCommissioned:
=DMax([Date_Commissioned], [tblUnit], "ID_Unit=" & [txtUnit])

=IIf(
	IsNull([txtUnit]),"",
	DLookUp("Make","tblUnit","([ID_Unit]=" & [txtUnit] & " And [Date_Commissioned]=" & "#" & [txtMaxDateCommissioned] & "#" & ")"))

 

[Nadine67]

**** SOLVE ****

Thank you once again stumac, welshgasman, and xenou!

You have done it gain, and I am very grateful.

Have a great day!