Using DLookUp in a query

[Bellacuse]

I have been banging my head on a wall for a while trying to solve this, and I am hoping someone can put me out of my misery! I have a query that draws from 4 tables and adds stock levels from "in" and "out" fields. However, a seperate table also tracks what stock is used on jobs. I have tried to add this data into the query via tables, but it throws everything out of whack no matter what I try. To get around this issue, I am trying to use dlookup to fill in the "stockused" portion.

This seems to work when I use "StockID" as the only criteria. But I need to also use "StorageID" as a second criteria and this is where I am running into a problem. there are multiple "StorageID" fields in seperate tables, so the query didn't like it. I renamed the query entry to "Store: StorageID" and used "Store" in my dlookup instead. Now when I run the query a popup prompts me to enter "Store". If I enter any number, the query runs and seems to work just fine (each return shows the correct number of stockused for the given stockid and storageid).

Why is the popup appearing??? So long as ANY number is entered (even numbers that have no relation to any storageids in any table), the query runs properly (text crashes the program).

My dlookup is: Expr4: DLookUp("[sumofqtyused]","[qrysumstockused]","benchstockidfk = " & [stockid] & " AND storageID = " & [store])

If it helps, my query (qrySumStockfm) is:
SELECT tblStockStorageMM.StockID, Sum(Round([QtyIn],3)) AS Expr1, Sum(Round([QtyOut],3)) AS Expr2, tblBenchStock.Nomenclature, tblStorage.StorageArea, tblLocation.LocationName, tblStorage.StorageID AS Store, tblLocation.LocationID, tblBenchStock.RedLevel, tblBenchStock.TargetLevel, tblBenchStock.[P/N], tblBenchStock.Manufacturer, tblBenchStock.UnitofIssue, tblBenchStock.QtyPerUnit, tblBenchStock.Cost, Sum((Nz([QtyIn])-Nz([qtyout]))*Nz([qtyperunit])) AS TotalStockPerItem, DLookUp("[sumofqtyused]","[qrysumstockused]","benchstockidfk = " & [stockid] & " AND storageID = " & [store]) AS Expr4
FROM (tblLocation INNER JOIN tblStorage ON tblLocation.LocationID = tblStorage.LocationID) INNER JOIN (tblBenchStock INNER JOIN tblStockStorageMM ON tblBenchStock.BenchStockID = tblStockStorageMM.StockID) ON tblStorage.StorageID = tblStockStorageMM.StorageID
GROUP BY tblStockStorageMM.StockID, tblBenchStock.Nomenclature, tblStorage.StorageArea, tblLocation.LocationName, tblStorage.StorageID, tblLocation.LocationID, tblBenchStock.RedLevel, tblBenchStock.TargetLevel, tblBenchStock.[P/N], tblBenchStock.Manufacturer, tblBenchStock.UnitofIssue, tblBenchStock.QtyPerUnit, tblBenchStock.Cost, DLookUp("[sumofqtyused]","[qrysumstockused]","benchstockidfk = " & [stockid] & " AND storageID = " & [store])
ORDER BY tblBenchStock.Nomenclature;

I'd appreciate any help on this!

 

[plog]

"in" and "out" fields

Usually that's the wrong way to handle an inventory system. You don't want different columns to hold the quantity for the type of transaction. Instead you should have one column for the quantity (and a transaction type if necessary):

YourTableNameHere
StockID, TransactionType, Quantity
3, Restock Inventory, 7
3, Sale, -2
12, Initial Inventory, 41
3, Damaged Goods, -3
12, Sale, -26

Then when you need total amount you just sum the quantity field.

Additionally, there's really no reason to use a Dlookup--just bring qrysumstockused into your query and link it appropriately. This will eliminate the need to get the criteria correctly in the Dlookup--you just link it with the fields it should be linked to.

 

[Bellacuse]

Thanks Plog. I tried bringing qrysumstockused into the query, but I couldn't make it work. It would recount all the rows and I'd end up with doubled, or tripled totals on other summed fields. Same type issue as trying to use the table for tracking items used in the query. This is why I am resorting to dlookup. This has almost solved the problem, except for the popup prompt which I cannot, for the life of me, figure out.

Part of the problem is that I am tracking the same part IDs in multiple storage locations, and I need to be able to track them separately. I am also dealing in units (say, a box of 500 connectors) as well as individual items (i.e. using 4 connectors on one job).

I'll try using a single column as you suggested though.

 

[Bellacuse]

I converted my stock/storage table to use 1 qty column per your suggestion, and lo and behold, the query works properly now - without dlookup. I guess the extra qty column was causing the query to "recount" those specific stockids and throwing things out of whack.

Thanks Plog!