Sorting Reports by Date, Part 1

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

How to sort reports by date in Access.

Access Archon #163


Introduction

If you have a report with a date field, you may need to sort the report by date, and Access has a built-in feature that lets you do date sorting very easily, without preparing a query in advance, or writing code.  This article shows you how to use the automatic date sorting feature for reports, and also how to write query expressions or VBA code to do more complex date sorting when needed.


Built-in Date Grouping

The sample database, Sorting Reports by Date (AA 153).mdb, has a table (tblMembers) with several date fields, for use in sorting reports.  One (BirthDate) is a standard Date field, containing the date of birth of a member.  Another (BirthYear) contains the member’s birth year as a Text value (yes, this could be derived from BirthYear, but I included this field, since the year may be all that needs to be stored in a table).  The third field is SignupAge, a Number field that contains the member’s age at signup.  Again, this value could be derived from the BirthDate field, but in some cases this information may be all that is needed (or you may be working with a database created by somebody who didn’t understand that a date field would be better).  In any case, Access has the flexibility to work with date-type data stored in fields of different data types.

There are two reports that sort by BirthDate, using two different built-in sort groups:  rptMembersByCalendarAgeMonth and rptMembersByCalendarAgeQuarter.  The first, rptMembersByCalendarAgeQuarter, has “Month” selected for the Group On property in the Sorting and Grouping dialog, as shown in Figure A, and “Whole Group” for the Keep Together property.

AA163 A - Sorting Reports by Date, Part 1

Figure A.  Grouping on Month for a Report

The BirthDate group header section has a subtitle that uses the Format function to display the month and year from the BirthDate field:

Format([BirthDate],”mmmm yyyy”)

This report is shown in Figure B:

AA163 B - Sorting Reports by Date, Part 1

Figure B.  A report grouped by birth month

The rptMembersByCalendarAgeQuarter has “Qtr” selected as the Group On value in the Sorting and Grouping dialog; it uses the DatePart function to extract the quarter for a subtitle, using this expression:

“Q ” & DatePart(“q”,[BirthDate]) & “, ” & Year([BirthDate])

This report is shown in Figure C:

AA163 C - Sorting Reports by Date, Part 1

Figure C.  A report grouped by Birth Quarter

The rptMembersByBirthYear report uses the BirthYear field for sorting; it uses the default “Each Value” selection for the Group On value in the Sorting and Grouping dialog, and just displays the value of the BirthYear field in the group header.  This report is shown in Figure D:

AA163 D - Sorting Reports by Date, Part 1

Figure D.  A Report sorted by Birth Year


Supporting Files

The zip file containing this article, in Word format, plus the supporting file, may be downloaded from the Access Archon page of my Web site.  It is accarch163.zip, which is the last entry in the table of Access Archon columns for Access Watch.











Document Name

Document Type

Place in

Sorting Reports by Date (AA 163).mdb

Access 2000 database (can also be used in higher versions of Access)

Wherever you want

 

subs profile e1563205311409 - Sorting Reports by Date, Part 1
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address