DLookUp with multiple criteria (1 Viewer)

sandy70

Registered User.
Local time
Today, 10:00
Joined
Apr 16, 2008
Messages
36
I have queries like this
Screenshot 2020-11-10 23.29.37.png


is it possible to lookup the price base on the date and product ???
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:00
Joined
Oct 29, 2018
Messages
21,358
Of course. For example:
Code:
DLookup("Price","PriceList","Des='Pro 01' AND StDate=#01-01-20#")
Hope that helps...
 

sandy70

Registered User.
Local time
Today, 10:00
Joined
Apr 16, 2008
Messages
36
Of course. For example:
Code:
DLookup("Price","PriceList","Des='Pro 01' AND StDate=#01-01-20#")
Hope that helps...
so i try using this DLookUp("Price","[PriceList]","Des=[Des] AND StDate=#" & [Date01] & "#")

and the result like this
Screenshot 2020-11-11 00.26.10.png


something wrong ???
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:00
Joined
Oct 29, 2018
Messages
21,358
so i try using this DLookUp("Price","[PriceList]","Des=[Des] AND StDate=#" & [Date01] & "#")

and the result like this
View attachment 86532

something wrong ???
Try:
Code:
DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate=#" & [Date01] & "#")
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:00
Joined
Aug 30, 2003
Messages
36,118
In a query it would be more efficient to join the tables than use a DLookup(). You'd want a non-equi join.
 

sandy70

Registered User.
Local time
Today, 10:00
Joined
Apr 16, 2008
Messages
36
Try:
Code:
DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate=#" & [Date01] & "#")
i try that and the result :
Screenshot 2020-11-11 00.55.49.png

the 17 jan 20 Pro 03 is empty

and i try to add 03 Feb 20 and empty too
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:00
Joined
Oct 29, 2018
Messages
21,358
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
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?
 

sandy70

Registered User.
Local time
Today, 10:00
Joined
Apr 16, 2008
Messages
36
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?
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:00
Joined
Oct 29, 2018
Messages
21,358
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
Hi. Thanks for the clarification. I guess I missed the EndDate part earlier. Give this one a try.
Code:
DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate<=#" & [Date01] & "# AND EnDate>=#" & [Date01] & "#")
Hope that helps...
 

sandy70

Registered User.
Local time
Today, 10:00
Joined
Apr 16, 2008
Messages
36
Hi. Thanks for the clarification. I guess I missed the EndDate part earlier. Give this one a try.
Code:
DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate<=#" & [Date01] & "# AND EnDate>=#" & [Date01] & "#")
Hope that helps...
tq so much , its done

Screenshot 2020-11-11 07.32.33.png
 

plog

Banishment Pending
Local time
Today, 12:00
Joined
May 11, 2011
Messages
11,611
Are you using this Dlookup in a query? You keep posting tabular data as if you are. If so that is the incorrect method. DLookups do not belong in queries, you use a JOIN.

Specifically, you use the JOIN pbaldy mentioned in post #8
 

Users who are viewing this thread

Top Bottom