Link to home
Start Free TrialLog in
Avatar of Lawrence Salvucci
Lawrence SalvucciFlag for United States of America

asked on

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

Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Try with this criteria:

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

Open in new window

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.
Avatar of Lawrence Salvucci

ASKER

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?
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)
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
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
Glad I could help, Lawrence.

Always enjoy a good challenge.  Fortunately, you had that MaterialClassID value in tblClass1