Group a calculated field by month in Access Report 2

[monkeysee]

I have a report which has a calculated field - see attached.
What I would like to have this report do, is to print a month at a time using this calculated field.
Usually this is easily done in sorting and grouping, however, since this is a calculated field on the report itself, I do not have the option. Any thoughts?

 

[MajP]

if this field is calculated in the query instead of the control you could group on it.

 

[dhookom]

You can also group on expressions but I like MajP's suggestion of placing the calculation in the query.

Another observation is you are storing business calculations in an expression in a report. I recommend a table of priorities with the number of months to add to the last inspection date. Then when your values change you won't have to change report, form, or query designs. Data belongs in your tables, not your expressions and code.

Minimally I would create a small function that accepts the priority and last inspection date and returns the next inspection date. Save the function in a standard module named "modBusinessCalcs".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

Thanks MajP and dhookom! Both are great ideas! I will investigate both. as far as storing the result of the expression in the table; this is not data I want to keep as it will change based on the LastInspection. This report was designed as a guideline for the inspectors to plan their month, etc. The actual reinspection date may not be the same date as the result in the expression. The actual re-inspection date will be saved in the table. I should have actually described the usage of the report when I posted last night, but it was late and was not firing all pistons at the time. But I do want to thank both of you, you were great help! Stars for both!

 

[dhookom]

I wasn't suggesting "storing the result of the expression in the table". I was suggesting having a table like:

[pre]tblPriorties
[Priority] [MthsToNextInsp]
1 4
2 12
3 24
[/pre]

You would then JOIN tblPriorties into your report's record source so your expression would simply be:
NextInspectionBy: DateAdd("m",[MthsToNextInsp],[LastOfInspectionDate])

This is both simpler and allows you to maintain the intervals in data rather than expressions. Never assume these values will stay the same over the life of an application.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

That IS an AWESOME idea! Thanks!; makes a lot more sense and an easier calculation!