Sorting Reports by Date, Part 2

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.

How to sort Reports by date in Access.

Access Archon #163

You might need to group records by date using another interval that is not available in the Group On list.  If the interval is regular, you can create a query field, such as the Decade field in qryMembers:

Decade: Left(Format([BirthDate],”yyyy”),3) & “0”


The rptMembersByCalendarageDecade report is shown in Figure E:

Figure E.  A report sorted by decade

The last report needs to sort the members into several groups, which don’t all contain the same number of years.  Because of this, the built-in date grouping is not adequate, and special code must be written to do the sorting.

VBA Code

The sorting is done using a function with a Select Case statement:

Public Function AgeCategory(intAge As Integer) As String


On Error GoTo ErrorHandler


   Select Case intAge


      Case 18 To 24

         AgeCategory = “Age Category 1 (18-24)”


      Case 25 To 39

         AgeCategory = “Age Category 2 (25-39)”


      Case 40 To 54

         AgeCategory = “Age Category 3 (40-54)”


      Case 55 To 69

         AgeCategory = “Age Category 4 (55-69)”


      Case 70 To 84

         AgeCategory = “Age Category 5 (70-84)”


      Case 85 To 120

         AgeCategory = “Age Category 6 (85-120)”


      Case Else

         AgeCategory = “Unknown”


   End Select



   Exit Function



   MsgBox “Error No: ” & Err.Number & “; Description: ” & _


   Resume ErrorHandlerExit


End Function


The function is in turn used to create a custom field in a query (qryMembers) that is the report’s record source:

Category: AgeCategory(CInt([SignupAge]))


The resulting report is shown in Figure F:

Figure F.  A report sorted by custom age categories

With these techniques, you should be able to sort any type of date fields as you wish.


The code in the sample database does not need any special references.

Supporting Files

The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is, 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


Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.