expression builder on report to select MAX(later) date 1

[davyre]

Hi,

I have a textbox in a report named ETD (Estimated Time Delivery). The record source for the textbox is

=IIf([OrderMethod]="Stock","ASAP",Max(DLookUp("[ETD]","TmpTblOrderUnitPartStatus","[UnitID]=" & [UnitID] & " And [OrderID]=" & [OrderMethod])))

note that the code is in Expression Builder, not in the VBA

the code is trying to see IF the textbox named [OrderMethod] has value of "Stock" then the ETD is "ASAP". Otherwise,
look for the ETD in Table TmpTblOrderUnitPartStatus where UnitID=UnitID and OrderID=OrderMethod.

In my case, there are some duplicates in the table TmpTblOrderUnitPartStatus where UnitID and OrderID are the same, but PartID (In one unit there are many parts) are different. SO what I want the compiler to do is to select the maximum/later DATE in field ETD in TmpTblOrderUnitPartStatus where UnitID=UnitID and OrderID=OrderMethod.
I already have two examples of the same UnitID and OrderID, one dated 01/21/2013 and the other 01/26/2013. What I want is to select the later (01/26/2013), but currently its not happening (it selects the first one 01/21/2013).
Anyone can help? Thanks

 

[PHV]

What about this ?
=IIf([OrderMethod]="Stock","ASAP",DMax("[ETD]","TmpTblOrderUnitPartStatus","[UnitID]=" & [UnitID] & " And [OrderID]=" & [OrderMethod]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

it worked just as I need. THanks!