Using Dsum To Show Individual Product Totals in a Report

[David Randle]

Hi

I have a report that shows all items contained in a stock table.

I am trying to use the dsum function with two criteria to provide the current stock holding of each individualproduct in the report, (line by line).

The criteria are based on a unique field (productId) and a number (inOrOut) which represents whether the stock has been added or deleted.

The expression I am using is:

= DSum("Quantity", "StockTbl", "ProductId = " & [Reports]![StockMovementRep]![ProductId] _
& "AND InOrOut = 1") - DSum("Quantity", "StockTbl", "ProductId = " & [Reports]![StockMovementRep]![ProductId] _
& "AND InOrOut = 2")

I have used same expression, using [Forms] to populate a field on a form and it works fine. However, it does not do the same thing on the report.



Would appreciate some assistance with this.

Many thanks

David

[Micron]

Several issues could be at play here, some of which might not rear their ugly heads until you solve the one before the next issue. More information about what you are doing may be necessary if none of this solves the issue.
1) you provide the data type for one field ... and a number (inOrOut) but not the other. If ProductId is text, you are missing the necessary quote marks.

2) since there are many ways and places to put calculated controls on a form or report, your prior success may not be something that you can replicate here by simply employing similar syntax in an expression. The method to use depends on things like where the calculated control is placed (header, footer or detail section) and what section of the report/form the source controls are. If your calculation a line item (i.e. you want it to appear line by line) this is best done in the report's underlying query AFAIC. Your references would then be based on table fields, not report controls. To do this line by line in a report anyway, you should not need the reference to the report object, just the fields of the underlying recordset ([controlName1] - [controlName2]). I believe the DSum function is not the right choice for a line by line calculation, since that is meant to be used over a domain. Your post suggests the calculation is being done line by line.

3) if you use a wizard to build a report or form, Access will name your controls the same as their related fields. When attempting to add aggregate functions in calculated controls that you add afterwards, Access sometimes does not handle the calculations because of this. I have not read or determined if Access fails on the calculation because of the ambiguity (no error is generated) or if it's because a referenced control has no data when the calculation is made (but the field does). I will use a wizard, but I ALWAYS go through the controls and rename them using my naming convention before doing anything else in order to prevent this. I cannot tell if your controls and their bound fields are named the same.