Sum not showing up

[sondriven]

I searched in Report forum and couldnt find help, and figured this is a general topic.

I have a report with a subform in it. I want to run a sum of a field in the subform. I made a text box inside the subform footer. And put this expression for the control:

=Sum([Total Cost])

The field box is named Total Cost. When I go to preview the report the Sum text box is empty.

Why is this?

 

[fernin8r]

It usually means that there is a null in your field that it can't add.

If this is the case, try using the Nz() function.

 

[Pat Hartman]

[Total Cost] sounds like a calculated control to me. If so, you cannot use the names of controls in an aggregate function. You will need to repeat the calculation:

=Sum(OrderQty * UnitPrice)

By way of explaination, aggregate functions work accross an entire domain. In your case, you are summing the [Total Cost] of a number of rows. Access does not, nor could it reasonably be expected to, store the values of all controls for all rows of a recordset in memory. Controls only exist once and therefore they can only hold the value of the current record. That is why something like [OrderQtyControlName] * [UnitPriceControlName] will work but Sum(TotalCost) will not. The first expression refers to two controls in the current record but the Sum() requires access to ALL records of the domain not just the current record.

 

[sondriven]

Yes I have Quantity and Unit Cost fields.

In the Total Cost field is the expression:

=[Quantity]*[Unit Cost]

I think what youre telling me is that Access cant take this further and give me a sum of the Totals for a final cost.

Is there any way of doing this? I tried your expressions and they didnt seem to work.

Thanks.

John

 

[Pat Hartman]

No, what I said was that you have to redo the calculation within the Sum() function and I gave you an example. Here is one using your own field names -

=Sum([Quantity]*[Unit Cost])

If you just pasted my example, of course it didn't work. You needed to use your own column names. If your column names are [Quantity] and [Unit Cost], then you should be able to copy and paste the above expression.

 

[sondriven]

Hey Pat,

Of course I didnt try to use your column names. But what was missing was the parenthesis.

Got it to work now. Thanks a mil.

John