Report Page Footer Sum Error

Question

In Design View of a report I have this enter image description here

In the fourth column, those two "=SUM([SUM...." text boxes both say "=Sum([SUM660201])". The column name is "SUM660201".

On the Report View however, only the first text box (the one in the detail and not in the page footer, correctly calculates the sum). In the Page view, it says "#Error" rather than "257.71"

enter image description here

I want the sum to be calculated in the Page Footer, not in the Detail. How do I make the sum in the Page Footer Work?

Did you try to remove it from the Detail? Did you build this report with the Wizard or what? — Mark C.
@OverMind, I built this report with the wizard. Orginially I had the text box in the footer, and it didn't show. then I tried putting it in the Detail, and saw that it did accurately display there. — johnsimer

Brad Brad · Accepted Answer · 2014-07-29T23:09:15

From here

The page footer and header sections, however, do not support calculated controls that use aggregate functions such as Sum

You can however use VBA to calculate page aggregates. The linked MS article explains how. The summary of their instruction is this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  If PrintCount = 1 Then
    txtPageSum = txtPageSum + ExtendedPrice
    txtPageQuantity = txtPageQuantity + Quantity
  End If
End Sub

Access increments [the PrintCount] property by one whenever the data for the current section is printed. Because there are times when the Print event for the Detail section for a particular record might be called more than once, checking the PrintCount value ensures that you don't add the same value twice to a page total.

Reset the numbers for the next page print

Private Sub PageHeaderSection_Print(Cancel As Integer, _
                                    PrintCount As Integer)
  txtPageSum = 0
  txtPageQuantity = 0
End Sub

Again, see the MS help page for more details.

Report Page Footer Sum Error

1 Answers