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
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.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.
References
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 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 |
