Using Domain Aggregate Function in an IIf Statement 1

[pjd218]

Any guidance on syntax?

What I am trying to do is to lookup the last value in a related table based on a field(DateChange)and then use another value(s)to perfom misc. calcs on a form, report, and query.

I do not want to affect any values older than the ChangeDate

 

[Remou]

Have you looked at DMax?

 

[pjd218]

Remou,

DMax works to pull the most recent ChangeDate, but I then need to pull a field from that record to perfom a calculation.

Any suggestions?

 

[PHV]

DLookUp("[a field]","yourTable","ChangeDate=#" & DMax(...) & "#")

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

 

[pjd218]

PHV

Thanks for the suggestion. Appears to be working.

However, if I have a price change date from the PriceTBL for month other than the current month, getting an Error? on the calculation. Code follows:

=[BagIceSales]/DLookUp("[BagIcePrice]","PriceTBL","ChangeDate=#" & DMax("ChangeDate","PriceTBL","[ChangeDate]<=Forms!SalesFRM![SalesDate]") & "#")

 

[PHV]

Hopefully ChangeDate and SalesDate are real dates ?

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

 

[pjd218]

PH,

Nice work.

Seems to do the trick. Not sure about that error but I may have been looking at the wrong field. Too tired.