Average of Calculated Field in Report

[OlBill]

Hello all. Hope you are all doing well. I have a calculated field in my table and query. It is the number of days between to dates. Works fine. I can get the average at the bottom of my query, but I can't figure out how to get that average onto my report. Is there a way to do this?

I fully expect to get chastised for using calculated fields.

Thanks in advance for the help.

[orange]

Bill,

Did you review the Similar Threads at the bottom of this page?

[Welshgasman]

You use the Totals button and the AVG option, surely?

[June7]

In report or group header, textbox calculation: =Avg(fieldname)

[OlBill]

Thanks orange. I tried to do a search before I posted.

[OlBill]

I tried that, it doesn't work. Perhaps because the field is calculated? I have a [start] and [end], both dates. [Days] calculates the number of days between the two. That works.
I can go the query and get the average of [Days] there, I can't figure out how to get that AVG on to the report.

[OlBill]

I tried that, it doesn't work. Perhaps because the field is calculated? I have a [start] and [end], both dates. [Days] calculates the number of days between the two. That works.
I can go the query and get the average of [Days] there, I can't figure out how to get that AVG on to the report.

[OlBill]

Bill,

Did you review the Similar Threads at the bottom of this page?

I saw the post by @pbaldy that seems very close. The link doesn't work. Error - Office.com - Microsoft Support

[June7]

I have no problem with textbox aggregate calculation with table calculated field.

If you want to provide db for analysis, follow instructions at bottom of my post.

[OlBill]

I have no problem with textbox aggregate calculation with table calculated field.

If you want to provide db for analysis, follow instructions at bottom of my post.

Thank you. I'll do some research on that if I still don't get it, I'll put up a practice DB for help next week.
Y'all have a great weekend.

[OlBill]

I have no problem with textbox aggregate calculation with table calculated field.

If you want to provide db for analysis, follow instructions at bottom of my post.

Here is a mockup I made. I get the same error in my actual.

[June7]

Works in Report Header/Footer or Group Header/Footer (as advised to use in post #4), not Page Footer.

If you want to aggregate each page, review https://answers.microsoft.com/en-us/...c-014c06374408

I have this code behind report in my db:

Option Compare Database
Option Explicit
Public intPageCount As Integer
Public intPageTotal As Integer


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   ' increment intPageCount and intPageTotal for each record printed
    intPageCount = intPageCount + 1
    intPageTotal = intPageTotal + Nz(Me!Qty, 0)
End Sub


Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'set the textboxes in footer
    Me.tbxPageCount = intPageCount
    Me.tbxPageTotal = intPageTotal
    'reset our counters
    intPageCount = 0
    intPageTotal = 0
End Sub

Difference is I don't set RunningSum property on UNBOUND textboxes in Page Footer. Maybe I should but report is only 1 page and have not tested for multiple pages.

[OlBill]

Works in Report Header/Footer or Group Header/Footer (as advised to use in post #4), not Page Footer.

If you want to aggregate each page, review https://answers.microsoft.com/en-us/...c-014c06374408

I have this code behind report in my db:

Option Compare Database
Option Explicit
Public intPageCount As Integer
Public intPageTotal As Integer


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   ' increment intPageCount and intPageTotal for each record printed
    intPageCount = intPageCount + 1
    intPageTotal = intPageTotal + Nz(Me!Qty, 0)
End Sub


Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    'set the textboxes in footer
    Me.tbxPageCount = intPageCount
    Me.tbxPageTotal = intPageTotal
    'reset our counters
    intPageCount = 0
    intPageTotal = 0
End Sub

Difference is I don't set RunningSum property on UNBOUND textboxes in Page Footer. Maybe I should but report is only 1 page and have not tested for multiple pages.

Thank you. I will try your link tomorrow. Much appreciated.

[OlBill]

Well, I tried it and got zeros and the error. I guess I'm not smart enough to do this.

[June7]

Here's what I did with your posted db.

1. moved Text11 to Report Footer section

2. created UNBOUND textboxes in Page Footer section: tbxPageTotal, tbxPageCount. tbxPageAvg

3. expression in tbxPageAvg: =[tbxPageTotal]/[tbxPageCount]

4. pasted my code into report code module and changed Me!Qty to Me!Days

5. added enough records to table to generate multi-page report for testing

6. open report to PrintPreview

I also changed report RecordSource to tblDays because I saw no benefit to using query.