Incorrect SUM values in section footer

[Steve S]

I have a report where I need to sum fields in a footer as shown below. The
“Contest total†field is calculated as =Sum([Event Fees]) + [Admin Fee] and
is always correct. Both fields are from the record set. First I tried to
calculate Student Total as = Sum([Event Fees]) + Sum([Admin Fee]) but the
result was not correct because it summed every occurance of Admin Fee since
it was part of the record set.

Contest A Admin Fee $5.00
Basic Strut Novice 10-12 $15.00
Solo Beginner 10-12 $10.00
Contest Total $30.00

Contest B Admin Fee $5.00
Basic Strut Novice 10-12 $9.00
Contest Total $14.00

Student Total $44.00


I have tried to calculate the Student Total as = Sum([Contest Total]) in a
test box but when I preview the report I get the error message asking me to
input the value for “Contest Totalâ€. It seems that I can only reference
fields from the record source in the section footer but not calculated
fields. How do I get around this? Any suggestions appreciated.

Steve

 

[Larry Linson]

No, that is not correct: you cannot use the builtin functions such as SUM on
calculated Controls, that is, where the calculation is done in
ControlSource; you can use them on calculated Fields in the Report's
RecordSource, because those appear no different to the Report than a Field
from a Table.

You have a few options: calculate the value in the Query or SQL that is the
Report's RecordSource, use VBA code to save into variables calculated values
from the Footer where you SUM them and additional VBA code to SUM those
again, or use a domain aggregate function, such as DSUM in the Control
Source of a Control.

Your data and requirements should make it obvious which you should use in a
given situation.

Larry Linson
Microsoft Access MVP

 

[Duane Hookom]

You can set a running sum on a text box. Then in the report footer, add a
text box with a control source like:
=[Name Of Running Sum Text Box]