Report Footer Average Function (1 Viewer)

[Eljefegeneo]

I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer. My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal. This is in the DateFooter section of the report. I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]). Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal]. I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn’t ask me for an input, there was nothing in the ubound control in the report footer. I have search for an answer, but all I find is using a query.
[FONT=&quot]Is what I am attempting to do possible? If so, how? Thanks[/FONT]

 

[vbaInet]

You can't use domain aggregate functions (like Avg) to calculate on values that are not in the record source.

Perform the MonthlyTotal calculation in the record source (i.e. query) of your report and use that field to get the Avg in your report. Otherwise use DAvg().

 

[Eljefegeneo]

Thank you. Fortunately I only beat my head against a wall for an hour on this.

 

[vbaInet]

I hope that didn't cause too much damage

 

[Eljefegeneo]

No. just frustrating. I took your advice, did it in a query and guess what, too me about 5 minutes and it was solved.

 

[vbaInet]

Good to hear!