so i try using this DLookUp("Price","[PriceList]","Des=[Des] AND StDate=#" & [Date01] & "#")Of course. For example:
Hope that helps...Code:DLookup("Price","PriceList","Des='Pro 01' AND StDate=#01-01-20#")
Try:so i try using this DLookUp("Price","[PriceList]","Des=[Des] AND StDate=#" & [Date01] & "#")
and the result like this
View attachment 86532
something wrong ???
DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate=#" & [Date01] & "#")
non-equi join ???In a query it would be more efficient to join the tables than use a DLookup(). You'd want a non-equi join.
i try that and the result :Try:
Code:DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate=#" & [Date01] & "#")
non-equi join ???
Can u explain more ???
Those two dates and product combos are empty because you didn't have a matching record for them in your PriceList table. You can't "look up" something that's not there. If it's not available, what did you want to display instead?i try that and the result :
View attachment 86535
the 17 jan 20 Pro 03 is empty
and i try to add 03 Feb 20 and empty too
so there was prices for some period of date ( Start Date and End Date )Those two dates and product combos are empty because you didn't have a matching record for them in your PriceList table. You can't "look up" something that's not there. If it's not available, what did you want to display instead?
Hi. Thanks for the clarification. I guess I missed the EndDate part earlier. Give this one a try.so there was prices for some period of date ( Start Date and End Date )
so what i like to do is when Date= 03 Feb 20 and Des= Pro 02 , the query looking for the price for Pro 02 that the date in the range 17 Jan 20 to 26 Feb 20
So 03 Feb 02 is in between 17 jan 20 and 26 feb 20 , the price of Pro 02 should be 7
sorry for my bad english
DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate<=#" & [Date01] & "# AND EnDate>=#" & [Date01] & "#")
tq so much , its doneHi. Thanks for the clarification. I guess I missed the EndDate part earlier. Give this one a try.
Hope that helps...Code:DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate<=#" & [Date01] & "# AND EnDate>=#" & [Date01] & "#")
Hi. Glad to hear you got it sorted out. Good luck with your project.