Dlookup Syntax in MS Access Query

I am trying to get a DLOOKUP to work in a select query in ms access. The fields "RawDiameter" & "StockRemoval" are numeric in tblClass1. The fields "RMDia" & "StkRemoval" in my query are text so I converted them to numeric in my dlookup. And then the field "MaterialClass" is a text value in tblClass1. And "GrindClass" is also a text value in my query. But when I try to run my query the column where the DLOOKUP is comes out all blank. Is the syntax incorrect for my dlookup?

SELECT dbo_vwJobList.JobNo, dbo_vwJobList.Quantity, dbo_vwJobList.GroupCode, dbo_vwJobList.FGDia, dbo_vwJobList.SetUpMins, dbo_vwJobList.ProdMins, dbo_vwJobList.Passes, dbo_vwJobList.RMDia, dbo_vwJobList.FGTol, dbo_vwJobList.GrindClass, dbo_vwJobList.StkRemoval, DLookUp("[GrindTimeS1]","tblClass1","[RawDiameter] >= 'Val([RMDia])' And 'Val([StkRemoval])' <= [StockRemoval] And [MaterialClass] = '[GrindClass]'") AS Pass
FROM dbo_vwJobList;

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try with this criteria:

"[RawDiameter] >= " & Val([RMDia]) & " And & " Val([StkRemoval]) " & <= [StockRemoval] And [MaterialClass] = '" & [GrindClass] & "'"

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
In your post you indicate " The fields "RMDia" & "StkRemoval" in my query are text"
Does this mean the tblClass1 is actually a query, not a literal table?

Running DLookup (or any other domain function) within a query is highly frowned upon.  Among other things, DLookup has to be performed against each and every record in your recordset, which is time consuming.

Instead, try to create the query with a join:

SELECT JL.JobNo, JL.Quantity, JL.GroupCode, JL.FGDia, JL.SetUpMins, JL.ProdMins, JL.Passes, JL.RMDia, JL.FGTol, JL.GrindClass, JL.StkRemoval,
C.GrindTimesS1
FROM dbo_vwJobList  JL
LEFT JOIN tblClass1 C ON C.[RawDiameter] >= VAL(JL.RMDia)
AND C.StockRemoval = JL.StkRemoval
AND C.MaterialClass = JL.GrindClass

However, DLOOKUP will only return "a single value - if any" (not necessarily the first or smallest RawDiameter) and this query would return all of the values in tblClass1 which meet those critieria, so if there is more than one record in tblClass1 for a given StkRemoval, MaterialClass, and RawDiameter greater than JL.

So if you need the [GrindTimesS1] value associated with the record from tblClass1 with the minimum RawDiameter greater than JL.RMDia then you will need to rethink the DLOOKUP anyway, and would need to modify this query as well.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The tblClass1 is a table.

I am looking to return one row from the tblClass1 for each record in my query. I'm looking to match those 3 criteria's to one record and return the GrindTimeS1 value. I thought using DLookup would work to return the value. Is there another way to retrieve the GrindTimeS1 value where those 3 criteria's match? How would my query need to be designed to accomplish this?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
can there be more than one record in tblClass1 for a particular combination of StockRemoval and MaterialClass?

I note that in your DLOOKUP you indicated you wanted a value where the RMDia >= RawDiameter, which would imply that you could have more than one value.  If that is the case, do you want to select the record with the greatest  or lowest value of RMDia >= RawDiameter?

For that, you might want to modify the query something like:

SELECT JL.JobNo, JL.Quantity, JL.GroupCode, JL.FGDia, JL.SetUpMins, JL.ProdMins
, JL.Passes, JL.RMDia, JL.FGTol, JL.GrindClass, JL.StkRemoval, C.GrindTimesS1
FROM dbo_vwJobList  JL
LEFT JOIN tblClass1 C ON
AND C.StockRemoval = JL.StkRemoval
AND C.MaterialClass = JL.GrindClass
WHERE C.[RawDiameter] IS NULL
OR C.[RawDiameter] = (SELECT MIN(RMDia) FROM tblClass1
WHERE StockRemoval = JL.StkRemoval
AND MaterialClass = JL.GrindClass
AND RawDiameter >= JL.RMDia)
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
It's better if I show you the data so I'm explaining myself better. In the tblClass1 spreadsheet you will see the tblClass1 table structure in columns A to G. In columns L to N are the 3 fields in my vwJobList query with some example data. Then in column P is the GrindTimeS1 value I am looking to return one value for each record in the vwJobList query.

The RMDia should equal the RawDiameter or find the next RawDiameter that is larger than the RMDia. The StkRemoval should equal the StockRemoval or find the next StockRemoval that is larger than the StkRemoval. And the GrindClass should equal the MaterialClass. And if it doesn't match any records then it should just be null. So this way it finds the 1 row that best meets the criteria's outlined.
tblClass1.xlsx
Dale FyeOwner, Developing Solutions LLCCommented:
Does your JobList view contain a unique id or set of unique columns.

Assuming that you have a unique ID, this query would be a start point:

SELECT T.ID as JL_ID, MinMatClassID
FROM (
SELECT JL.ID, Min(C.MaterialClassID) as MinMatClassID
FROM vwJobList AS JL
LEFT JOIN tblClass1 AS C
ON (JL.GrindClass = C.MaterialClass)
AND (JL.StkRemoval <= C.StockRemoval)
AND (JL.RMDIA <= C.RawDiameter)
GROUP BY JL.ID
) as T

But if you don't have a unique ID field in vwJobList, then you would need

SELECT T.GrindClass, T.StkRemoval, T.RMDia, MinMatClassID
FROM (
SELECT JL.GrindClass, JL.StkRemoval, JL.RMDia, Min(C.MaterialClassID) as MinMatClassID
FROM vwJobList AS JL
LEFT JOIN tblClass1 AS C
ON (JL.GrindClass = C.MaterialClass)
AND (JL.StkRemoval <= C.StockRemoval)
AND (JL.RMDIA <= C.RawDiameter)
GROUP BY JL.GrindClass, JL.StkRemoval, JL.RMDia
) as T

This will give you all of the records from vwJobList and the minimum MaterialClassID from tblClass1 which meets the join criteria.

Then you take this query and join this back to the other two tables, like I've done in the attached image and pull in all of the fields you need from vbJobList and tblClass12018-12-06_Class_Job.jpgIf you have to use the 2nd query above, then you would join it back to vwJobList on all three of the GrindClass, RMDia, and StkRemoval fields, using equal joins.

HTH
Dale

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I did not have a unique ID in the vwJobList so I went with the 2nd approach you outlined. And it works like a charm! Never in a million years would I have thought to come up with this solution instead of using a DLOOKUP in my query. I greatly appreciate your time and effort into designing this query for me. Thank you so very much, Dale!
Dale FyeOwner, Developing Solutions LLCCommented:
Glad I could help, Lawrence.

Always enjoy a good challenge.  Fortunately, you had that MaterialClassID value in tblClass1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.