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.
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:
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:
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:
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 |
