W
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
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