Totals on a report

[Anne]

I have a report that has a description header for different categories of
services provided. I have added a calculated field in the description header
to give me a estimated time for completion of each category of work. I use
an average time specific to each category and the count of category. I want
to produce an overall total of the estimated times for all categories. So
far I get an error message. Any suggestions?

 

[Allen Browne]

You may be able to do this with running sum text boxes.

You say you have a text box in the description header to give the estimated
time. Assuming this text box is named txtEstTime, and this is a whole number
of minutes, add another text box in the same section, and give it these
properties:
Control Source =[txtEstTime]
Format General Number
Running Sum Over All
Visible No
Name txtDescripTimeRS

Add another text box in the same section, with properties:
Control Source =IIf([txtDescripTimeRS] Is Null, 0, 1)
Format General Number
Running Sum Over All
Visible No
Name txtDescripCountRS

Now add a text box to your report footer, and set its Contol Source to:
=IIf([txtDescripCountRS]=0,Null,[txtDescripTimeRS]/[txtDescripCountRS])

How it works
==========
txtDescripTimeRS accumulates the running sum of the estimated minutes.

txtDescripCountRS counts the number of description headers in the report
that had any estimated time value.

The text box in the report footer divides the total by the count to get the
average (avoiding the division by zero error.)