Dlookup Expression Help

[chrismja]

I am trying to adjust an expression in a query based on a Dlookup function. What I basically want the Dlookup function to do is look at the inventory days in stock and compare it with a table that has price adjustments based on a certain amount of days. I am including a sample of my database with the hopes that someone can help me figure out what i'm doing wrong.

[pbaldy]

I can't open the db (only have A2k here), but have you seen this?

DLookup Usage Samples

[chrismja]

Thanks for your reply. I have seen that. The problem is that every example I've seen shows the criteria being equal to something else, but not as a greater than or less than criteria. What I have put in as my expression is attempting to do what I want it to, but it is only returning the first "True" rather than finding the nearest match so to speak.

I've converted the database to Access 2000 and uploaded - hopefully you can help me.

[pbaldy]

Is it possible to change the structure of the Age_adj table to include the range:

180...209...300
210...239...400

[chrismja]

Yes, I had actually already did that on my end, but I still wasn't able to get it to work. I'll upload what I have...

Thanks for your help!

[pbaldy]

Two ways to get the same result. One is with what's called a non-equi join:

SELECT [TOTAL FORD INVENTORY].[STOCK NO], [TOTAL FORD INVENTORY].YR, [TOTAL FORD INVENTORY].MAKE, [TOTAL FORD INVENTORY].CARLINE, [TOTAL FORD INVENTORY].[MODEL DESCRIPTION], [TOTAL FORD INVENTORY].MODEL, [TOTAL FORD INVENTORY].DAY, [TOTAL FORD INVENTORY].[LST PRICE], ([EDGE INCENTIVES].[%]*[TOTAL FORD INVENTORY].[LST PRICE]-[Rebate])-(IIf([TOTAL FORD INVENTORY]![DAY]>179,(DLookUp("[Age_Adj]![Adjustment]","Age_Adj","[TOTAL FORD INVENTORY]![DAY]"<"[Age_Adj]![DAYS]")),0)) AS PRICE, Age_Adj.Adjustment
FROM Age_Adj RIGHT JOIN ([TOTAL FORD INVENTORY] INNER JOIN [EDGE INCENTIVES] ON ([TOTAL FORD INVENTORY].MODEL = [EDGE INCENTIVES].BODY) AND ([TOTAL FORD INVENTORY].YR = [EDGE INCENTIVES].YEAR)) ON (Age_Adj.MaxDays >= [TOTAL FORD INVENTORY].DAY) AND (Age_Adj.MinDays<= [TOTAL FORD INVENTORY].DAY)
ORDER BY [TOTAL FORD INVENTORY].[STOCK NO], [TOTAL FORD INVENTORY].DAY;

Note that in the join, rather than = I have >= and <=. That unfortunately limits you to SQL view. The other is your DLookup:

DLookUp("[Adjustment]","Age_Adj",("[MinDays]<=" & [TOTAL FORD INVENTORY]![DAY] & " AND [MaxDays] >= " & [TOTAL FORD INVENTORY]![DAY])

With a lot of data, the join method will be much more efficient that the DLookup.

[chrismja]

Thank you very much for your help. I will try that. Thanks!

[pbaldy]

Happy to help!

[chrismja]

I still couldn't get the DLookup to work as you had it- which is really what I'd rather have, however the SQL statement did work.

Thanks again

[chrismja]

Finally got the Dlookup to work! Thanks again...

[pbaldy]

Happy to help, and welcome to the site by the way!