Sum Fields

[Ladypep13]

I am trying to sum a field in a report that was based on a query. I am using
the following syntax in the footer of the report: =Sum([ContributionAmt]) but
it returns an error. I have looked this up in the Help database and this is
the syntax it says it has to be. Can anybody tell me what I'm missing?

I also need to be able to sum this same field on a form.

Thanks!

 

[Ken Sheridan]

A couple of possible reasons spring to mind:

1. ContributionAmt is not a column in the underlying query but a computed
control in the report. This could be an unbound control in the detail
section whose value is derived from two or more other bound controls, e.g.
=[Amount]*[Qty]. In which case you'd repeat the expression to sum it
=Sum([Amount]*[Qty]); or it could be a control in a group footer which sums
the detail to provide a sub-total with =Sum[Amount] say, in which case just
sum the detail in the report footer too with =Sum[Amount.

2. You are trying to sum in a page footer rather than a group or report
footer. If so you can't use the Sum operator in a page footer. A way to do
it, however, is in code:

2.1 Put an unbound control, txtPageTotal say, in the page footer.

2.2 In the Pageheader's print event procedure initialize it to zero with:

Me.txtPageTotal = 0

2.3 In the detail section's print event procedure increment the control with:

If PrintCount = 1 Then
Me.txtPageTotal = Me.txtPageTotal + Nz(Me.ContributionAmt,0)
End If

Examining the PrintCount property avoids any inadvertent double counting and
the Nz function caters for any rows where the ContributionAmt might be Null.

Ken Sheridan
Stafford, England

 

[Ladypep13]

Thank you, Ken, you were absolutely right. I was not putting the expression
in the page footer instead of the report footer. Been a long time since I
used Access. You can tell, huh? LOL. Thanks again!

A couple of possible reasons spring to mind:

1. ContributionAmt is not a column in the underlying query but a computed
control in the report. This could be an unbound control in the detail
section whose value is derived from two or more other bound controls, e.g.
=[Amount]*[Qty]. In which case you'd repeat the expression to sum it
=Sum([Amount]*[Qty]); or it could be a control in a group footer which sums
the detail to provide a sub-total with =Sum[Amount] say, in which case just
sum the detail in the report footer too with =Sum[Amount.

2. You are trying to sum in a page footer rather than a group or report
footer. If so you can't use the Sum operator in a page footer. A way to do
it, however, is in code:

2.1 Put an unbound control, txtPageTotal say, in the page footer.

2.2 In the Pageheader's print event procedure initialize it to zero with:

Me.txtPageTotal = 0

2.3 In the detail section's print event procedure increment the control with:

If PrintCount = 1 Then
Me.txtPageTotal = Me.txtPageTotal + Nz(Me.ContributionAmt,0)
End If

Examining the PrintCount property avoids any inadvertent double counting and
the Nz function caters for any rows where the ContributionAmt might be Null.

Ken Sheridan
Stafford, England

I am trying to sum a field in a report that was based on a query. I am using
the following syntax in the footer of the report: =Sum([ContributionAmt]) but
it returns an error. I have looked this up in the Help database and this is
the syntax it says it has to be. Can anybody tell me what I'm missing?

I also need to be able to sum this same field on a form.

Thanks!