Access Report group by date problem

This Access tutorial explains how to group by date (one date for each group) in Access Report and explain the issue.

You may also want to read:

Access Report keep a group together in one page

Access Report group by date problem

When you create an Access Report, the default setting isn’t group by date.

To illustrate, assume that we have an employee database as below.

Table1
Hire Date Employee ID
20/12/2016 001
20/12/2016 002
21/12/2016 003
21/12/2016 004
22/12/2016 005
1/1/2017 006

 

Our  goal is to create a report to group by date as below

Access Report group by date 02

 

Therefore we design a report like this

Access Report group by date 01

 

Click to preview, now instead of getting day by day result, somehow some dates are grouped together, this is different from our goal.

Access Report group by date 02

Access Report group by date – Solution

To find out the reason why, go back to Report Design View, at the bottom for the Grouping that you previously set for Hire Date, click “More” to see more available options.

Access Report group by date 03

You can see that by default, date is grouped by quarter, not by date. This explains why December dates are in one group while January date is in another group. (Q1 is Jan to Mar, Q2 is Apr to Jun, Q3 is Jul to Sep, Q4 is Oct to Dec)

To change the grouping back to date, select “by entire value” or “by day”

Access Report group by date 04

 

Click on preview, now you get the correct grouping by date.

Access Report group by date 05

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *