dlookup problem

[W]

Hi all,

I have a dlookup() problem (who hasn’t?) in a query.
The query has (amongst others) the following fields :

DateOfPurchase
Discount
Item
Price

The value of Price must be looked up in tblPricesPerUnit.

tblPricePerUnit has the following fields :

fldItemId (string), e.g. “123456†or “234567â€
fldBeginDate (date), e.g. 01/01/2007
fldEndDate (date), e.g. 31/12/2007
fldDiscount (string), e.g. “Y†or “Nâ€
fldfPricePerUnitNoDiscount (currency), e.g. 15.57
fldfPricePerUnitWithDiscount (currency), e.g. 13.57


The field I want to extract data from is fldfPricePerUnit.

I want to look up the price for an item (e.g. “456123†but given by the
query field ItemId) purchased on 04/17/2007 (given by the query field
DateOfPurchase - so between fldBeginDate and fldEndDate) and with a discount
(so the query field Discount).

I’d like to have the following syntax :
=dlookup(“fldfPricePerUnitâ€; “tblPricePerUnitâ€; “fldItemId = ‘ “ & Item & &
"'" _

& “ and iif(Discount = “Yâ€; fldfPricePerUnitWithDiscount ;
fldfPricePerUnitNoDiscount ) “ _
& “ and DateOfPurchase between #†& fldBeginDate# “ & “ and #" & fldEndDate
& "#")

Or something like that … #"

Anyone (a beginning of) a clue ?

Thanks beforehands,

W

 

[John Spencer]

I think you are looking for something like the following

DLookup(IIF([Discount ="Y"; "fldfPricePerUnitWithDiscount";
"fldfPricePerUnitNoDiscount"); "tblPricePerUnit"; "fldItemId='" & Item & "'
AND " & Format([DateOfPurchase],"\#yyyy-mm-dd\#") & " Between fldBeginDate and
fldEndDate")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County