Calculation Field in a Report

[Grimjack60]

Hello,


I have an unbound calculated field in my report [Days from Planned Submission]. It determines the number of days between 'today' and the date in another field of the same record [Planned Submission Date] by using the DateDiff command {=DateDiff("d",Date(),[MaxOfPlanned_Sub_Date])}. This field works fine. However, I am having trouble creating another calculation field at the bottom of the report which will compute the average for all the DateDiff records in this report. Any advice would be appreciated.

- Grimjack

[davegri]

Is the "bottom of the report" the report footer? If not, try it there.

[Grimjack60]

Is the "bottom of the report" the report footer? If not, try it there.

Yes, it is located in the Report Footer section. It is the coding I am having trouble figuring out. The first calculation field was easy. However, creating a second calculation field based on another calculation field is escaping me.

[davegri]

Instead of basing the final calculation on a previous calculated field, just repeat (include) the previous calculation in the final.
If that doesn't work, please provide more detailed information about the report's construction.

[Grimjack60]

The first field in my report selects most recent Planned Submission Date for a document {MaxOfPlanned_Sub_Date} from a table. The second field calculates the number of days between the day the report is run and the date in the Planned Submission Date field {=DateDiff("d",Date(),[MaxOfPlanned_Sub_Date])}. Now I need to create a field in the Report Footer that will calculate the average of all numbers in the second field.

[davegri]

Another approach might be to do the datediff calculation in the query that is the recordsource for the report.

[Grimjack60]

That idea worked, Dave. Thank you.

[davegri]

That idea worked, Dave. Thank you.

Been there, done that, and remembered it. Good luck with your project!