DSum not coming up with the correct Total

[epb]

Hello, I have an Inventory DB Table called [EngData] that has a subform table called [Inventory]. I have in the subform a calculated text box that Sums three columns, StockQtyMade, StockQtySold and StockQtyShrink. This works perfectly.



On the Main form I have a calculated text box that references the subform calculated field. This also displays correct info.

On another Form I wanted other users to see the total stock on hand by using a calculated field called PullTotalQty. This is set by using DSum(...). The only problem is that the DSum(...) keeps giving totals that are not accurate. Sometimes the quantity is a few pieces less then the correct amount and in other cases the qty is many times what it should be. Whatever the amount in error it is consistent each time a particular record is displayed. I have tried many things to get this to work but nothing has fixed the issue. I have also tried changing the query and nothing works. Changing the query and using the [Inventory] table in the DSum domain gives a different wrong quantity.

Here is the code: I reduced the code down to one column to be summed (StockQtyMade) just to see if I could get that working first. I will add the other two columns later. for testing purposes I am only looking at the StockQtyMade amounts.

Me.PullTotalQty.Value = DSum("[tblInventory subform].[Form].[StockQtyMade]", "[SumStockQuery]", "[tblInventory subform].[Form].[PartID]= " & Me.[ID])

Here is the SumStockQuery SQL:

SELECT Inventory.TransactionID, Inventory.PartID, Inventory.PrintNo, Inventory.StockRev, Inventory.StockQtySold, Inventory.StockQtyMade, Inventory.StockQtyShrink, Inventory.StockQtyXIncomplete, Inventory.StockLoc, Inventory.TransactionDescription
FROM Inventory
WHERE (((Inventory.TransactionDescription)<>"xNextOP"))
ORDER BY Inventory.TransactionID DESC;

Any Ideas on why the totals are incorrect??

[Bob Fitz]

Perhaps you need:

Me.PullTotalQty.Value = DSum("[StockQtyMade", "SumStockQuery", "PartID= " & Me.[ID])

[John_G]

Hi -

Bob is right (except for a missing "]" !) -

When you are using DSum, the field being summed should (Must?) be a part of the table/query you are drawing the data from, in your case "SumStockQuery". A form control is not part of the query, although it can *display* a field from the query.

The same is true for the criteria part - it should reference a field in the same table/query, not a form field, because if the form control does not have the right data source (from the table/query), who knows what you would get.

I'm surprised it doesn't give you an error, but I think Access is trying to figure out what you want, but doesn't get it right.

John

[epb]

Thank you, That has solved the issue!

I appreciate it. I spent hours trying to figure this out. Always something you learn through hard knocks.