DLookUp with multiple criteria

[sandy70]

I have queries like this

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

 

[theDBguy]

Of course. For example:

DLookup("Price","PriceList","Des='Pro 01' AND StDate=#01-01-20#")

Hope that helps...

 

[sandy70]

Of course. For example:

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

something wrong ???

 

[theDBguy]

so i try using this DLookUp("Price","[PriceList]","Des=[Des] AND StDate=#" & [Date01] & "#")

and the result like this
View attachment 86532

something wrong ???

Try:

DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate=#" & [Date01] & "#")

 

[pbaldy]

In a query it would be more efficient to join the tables than use a DLookup(). You'd want a non-equi join.

 

[sandy70]

In a query it would be more efficient to join the tables than use a DLookup(). You'd want a non-equi join.

non-equi join ???

Can u explain more ???

 

[sandy70]

Try:

DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate=#" & [Date01] & "#")

i try that and the result :

the 17 jan 20 Pro 03 is empty

and i try to add 03 Feb 20 and empty too

 

[pbaldy]

non-equi join ???

Can u explain more ???

An Illustrated Guide to the SQL Non Equi Join

SQL join doesn’t have to be based on identical matches! In this post, we look at the non-equi join, which uses ‘non-equal’ operators to match records.

learnsql.com

 

[theDBguy]

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]

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]

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.

DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate<=#" & [Date01] & "# AND EnDate>=#" & [Date01] & "#")

Hope that helps...

 

[sandy70]

Hi. Thanks for the clarification. I guess I missed the EndDate part earlier. Give this one a try.

DLookup("Price","PriceList","Des='" & [Des] & "' AND StDate<=#" & [Date01] & "# AND EnDate>=#" & [Date01] & "#")

Hope that helps...

tq so much , its done

 

[theDBguy]

tq so much , its done

View attachment 86549

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[plog]

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