×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Group a calculated field by month in Access Report
2

Group a calculated field by month in Access Report

Group a calculated field by month in Access Report

(OP)
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?

RE: Group a calculated field by month in Access Report

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

RE: Group a calculated field by month in Access Report

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

RE: Group a calculated field by month in Access Report

(OP)
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!

RE: Group a calculated field by month in Access Report

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

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

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

RE: Group a calculated field by month in Access Report

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close