databasedev.co.uk - database solutions and downloads for microsoft access

Sorting Microsoft Access Report Fields on the Fly:

There may be times when you wish to sort a report into a certain order by a specific field. Reports do not have the same freely available options that are available in Microsoft Access tables, queries and forms, where by the click of a button you can alter the sort order.

The report can be sorted when creating from scratch and also when going into design view to change, however you as the developer will not wish to allow the average user to change these options.

The following article describes, and includes an example, of how you can allow your users access to change the sort order across multiple fields of the report whilst in print preview.

The report used is just a standard report, created using the Microsoft Access report wizard, and includes no additional sorting options when created.

The Form:

The form used to sort the report by up to 6 options

This form contains options to allow the user to sort on 6 different fields. The default sort order is Ascending, however this can be set to print in Descending order.

To populate each of the Sort options, the combo boxes are set with a row source type of "Field List" and the Row source of:

SELECT [FirstName], [LastName], [Address], [Town], [City], [County]
FROM tblStudentInformation

This populates the combo box with a list of each field that the user is allowed to sort upon in the report.

In the On Open event of the form we will open the report in Print Preview, and with the form being set to Pop Up will ensure that the form stays on top of the report. The report opens using:

Private Sub Form_Open(Cancel As Integer)
'Open the report, maximized, in Print Preview
    DoCmd.OpenReport "rptStudentInformation", acViewPreview
    DoCmd.Maximize
End Sub

Report in Print Preview offering the different Sort options

Choosing from the options available and setting the sort order, you can then use the Set Sort Order command button to reorder the report to how you would like to view this.

Private Sub cmdSetSort_Click()

    Dim strSQL As String, intCounter As Integer
    'Build strSQL String
    For intCounter = 1 To 6
        If Me("cboSort" & intCounter) <> "" Then
        strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
        If Me("Chk" & intCounter) = True Then
            strSQL = strSQL & " DESC"
        End If
        strSQL = strSQL & ", "
    End If
    Next

    If strSQL <> "" Then
        'Strip Last Comma & Space
        strSQL = left(strSQL, (Len(strSQL) - 2))
        'Set the OrderBy property
        Reports![rptStudentInformation].OrderBy = strSQL
        Reports![rptStudentInformation].OrderByOn = True
    Else
        Reports![rptStudentInformation].OrderByOn = False
        
    End If

End Sub

This will build the Order By property of the report dependant on what options are selected and consequently re-order the report by what the user specifies.

To see this example, you can download the Microsoft Access 2000 database from the Microsoft Access Reports menu or from the Microsoft Access Downloads section.