Having trouble with several issues, can't seem to figure out what is going on

[Super Mario]

Dave,

The last field in the query should have the following expression: QTyOnHandCalc: Nz([SumOfUnitsReceived])-Nz([SumOfUnitsSold])-Nz([SumOfUnitsShrinkage])-Nz([SumOfUnitsUsed])+Nz([SumOfCycleCountCorrection])

Don't use spaces in the name Variable QTyOnHandCalc. The Nz() function will convert any NULL entry to 0 because if you add or subtract a NULL from a number the result will be NULL





I still maintain that Product ID 1 belongs to Mfg Part No. 19800 and has four records (if you apply a filter for Product ID = 1).

[Dave14867]

June7,

I had missed that post, sorry for that.

You posted "=Sum([UnitsReceived]) + Sum(Nz([CycleCountCorrection],0)) - Sum(Nz([UnitsSold],0)) - Sum(Nz([UnitsShrinkage],0)) - Sum(Nz([UnitsUsed],0))

If you decide not to output the raw details, then set the Detail Section as not visible."

I put that in the query and it works great.

I did not know about the setting to set the detail section as not visible, thanks for letting me know about that, I will explore that further.

I had tried the NZ prefix just before I left work and it was giving me the desired results but when I opened the report it was asking me about each field in the expression, and I just clicked thru the boxes and the report opened with the correct numbers.

This has helped me out immensely.

Thanks so much to all who replied.

Could either of you help me with putting this into a Function for reuse or wouldn't that be the best way, I am thinking about the Allen Browne Example, although he uses 6 tables and I use only 3 to achieve the same data ( not saying that mine is a better solution by any means, in fact it probably isn't), I like how he uses a Function, I just could not seem to modify his code to get it to work.

I have other forms that need the same info and will try to apply the same methodology to those.

Dave

[Dave14867]

Super Mario,

I am very confused, I can see that with the filter as you show, there does appear to be 2 different lines in the Inventory Transactions Table, however, if you open the Form "Inventory Transactions" PN 19800 is the first record and it only shows the opening balance of "0", It doesn't show the CycleCountCorrection qty of 35 because there is no PurchaseOrderID associated with that TransactionID of 2026.

Could you please open the form "Inventory Transactions" and let me know what you see? on that form?

Thanks

Dave