Dlookup in Report won't work in Query

[hmessing]

I have a dlookup in a report that works perfectly. I would like to use it in a query, however I can't seem to get it to work. I keep getting the same error: "The expression you entered as a query parameter produced this error: Microsoft Access can't find the name '2007 Smart Buy!2007 Commitment' you entered in the expression." It doesn't make sense to me because that field is in my query.

This is my dlookup expression:
DLookUp("[Level Discounts]![A]","[Level Discounts]"," [2007 Smart Buy]![2007 Commitment] > [Level Discounts]![Minimum] And [2007 Smart Buy]![2007 Commitment] < [Level Discounts]![Maximum] ")

Any suggestions would be greatly appreciated.

Thank you!!

 

[lameid]

Dlookups are inherently slow anyway.

I would recommend solving this as a query. You may need to make a query to replace the Dlookup function or more likely, add that table to your query and use criteria to accomplish the same thing. It is hard to say without knowing the table structure.

If you post your SQL, we can give you more specific suggestions.

 

[hmessing]

Wow! What a quick response. I'm not clear how I would make a query to replace the Dlookup function.

My database is used to create buying programs for our customers. Their discounts are based on the dollar commitment level for the year. We have three different levels A, B, and C (levels are determined by a shipment schedule). If a customer commits to purchase between $0 and $10,000 and chooses level A they get an additional 4% off of list price, level B - 2%, and level C - 0%. There are seven dollar ranges in addition each with a different discount %. Right now I have a report pulling the discount levels and it works great. I'm actually trying to solve another problem I can't seem to figure out by trying to get this dlookup to work.

Here is the SQL of the query:
SELECT [2007 Smart Buy].[Special%], [2007 Smart Buy].BPID, [2007 Smart Buy].[2007 Commitment], [2007 Smart Buy].Distributor, DLookUp("[Level Discounts]![A]","[Level Discounts]"," [2007 Smart Buy]![2007 Commitment] > [Level Discounts]![Minimum] And [2007 Smart Buy]![2007 Commitment] < [Level Discounts]![Maximum] ") AS a
FROM [2007 Smart Buy] INNER JOIN PROD_CUST ON [2007 Smart Buy].Distributor = PROD_CUST.USER_CUST_NO
GROUP BY [2007 Smart Buy].[Special%], [2007 Smart Buy].BPID, [2007 Smart Buy].[2007 Commitment], [2007 Smart Buy].Distributor, DLookUp("[Level Discounts]![A]","[Level Discounts]"," [2007 Smart Buy]![2007 Commitment] > [Level Discounts]![Minimum] And [2007 Smart Buy]![2007 Commitment] < [Level Discounts]![Maximum] ");

Hope all this makes sense. Thanks again.

 

[lameid]

Instead of Dlookup make a query...

Select [2007 Smart Buy].PROD_CUST, [Level Discounts].A
From [2007 Smart Buy], [Level Discounts] 
Where [2007 Smart Buy].[2007 Commitment] > [Level Discounts].[Minimum] And [2007 Smart Buy].[2007 Commitment] < [Level Discounts].[Maximum]

Name it.

Then add that query to your existing query and Outer Join to it. Add the field A from it instead of Dlookup.

Note that if your ranges overlap, this will return more than one value if the commitment meets both ranges. Dlookup would have just found the first one.

Also I noticed the table [2007 Smart Buy]. The name suggests to me that it contains just information for 2007. You would do well to just have a Smart Buy table with a year field. Then you would write all your queries to consider the year. This will save you the trouble of substituing all the [2007 Smart Buy] table names in your queries in 2008 with [2008 Smart Buy].

 

[PHV]

What about this ?
SELECT B.[Special%], B.BPID, B.[2007 Commitment], B.Distributor, L.A
FROM ([2007 Smart Buy] AS B
INNER JOIN PROD_CUST AS P ON B.Distributor = P.USER_CUST_NO)
INNER JOIN [Level Discounts] AS L ON B.[2007 Commitment] > L.Minimum AND B.[2007 Commitment] < L.Maximum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

As long as 2007 Smart Buy has a 2007 Commitment, PHV's solution is cleaner.

If however you want to allow the possiblity that 2007 Commitment is empty, stick with my solution. Both will cause the repeating record effect I noted.

 

[hmessing]

Both of those suggestions work perfect!!! Thank you so much! I really appreciate it!!

I need to learn more about SQL.