Lawrence Salvucci
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;
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.
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.
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?
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)
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)
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Always enjoy a good challenge. Fortunately, you had that MaterialClassID value in tblClass1
Open in new window