Sorting Reports by Date, Part 2

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

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:

AA163 E - Sorting Reports by Date, Part 2

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:

AA163 F - Sorting Reports by Date, Part 2

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

 

subs profile e1563205311409 - Sorting Reports by Date, Part 2
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