DlookUp Min(Date)

[terbs]

Im wondering if it was possible to run a Dlookup to find a value based on certain criteria such as non-primary ID field AND where the date is the earliest available.

I know you can do Min(Date) in a query, but is there any soution for a Dlookup?

Cheers..

 

[pbaldy]

You could use a DMin within the criteria of the DLookup.

 

[terbs]

Yeah I gave it a shot, but I get an error saying;

"Wrong number of arguments used with function in query expression"

Is this syntax you think I should have?

DLookup("[STOCKID]", "[TBLStock]", "[Status] = 21 AND [PARTID] = 756005 AND DMIN([PODateStamp])")

 

[gemma-the-husky]

mindate = Dmin("[podatestamp]", "[TBLStock]", "[Status] = 21 AND [PARTID] = 756005 AND is not null ([PODateStamp])")

then

DLookup("[STOCKID]", "[TBLStock]", "[Status] = 21 AND [PARTID] = 756005 AND podatestamp = mindate)")


will need formatting properly, and allowance for situations where there are no dates retrieved

 

[terbs]

Havign a bad day, should of thought of this.. thanks