How to change the sorting order of a report.
Q: Steve Rudder writes: “I have an Access report that the user would like to be able to change the sorting order. For example, some of the columns they’d like to sort on in the report are Start Date, End Date and a tracking number. Can I pass the sort parameter from a form to the report or the query that’s the record source for the report?”
A: Yes, this is quite possible, so long as the fields are sortable. My old Access Archon #41 (from 1999) describes how to do this. Here is the relevant portion:
To make it easier to select a sort field on-the-fly, create a dialog form with a combo box for selecting the sort field. Select Field List as the RowSourceType property for the combo box, and the report’s record source for the RowSource property. The following procedure (running from a Print command button) opens a report and sorts it by the field selected in the combo box:
Private Sub cmdPrint_Click()
On Error GoTo cmdPrint_ClickError
Dim strValue As String
Dim strFilter As String
Dim rpt As Report
strReportName = “rptCustomerData”
strFieldName = Nz(Me![cboSelectField])
If strFieldName = “” Then
MsgBox “Please select a field”
Me![strFieldName].SetFocus
End If
strSort = “Sorted by ” & strFieldName
Debug.Print “Sort: ” & strSort
DoCmd.OpenReport strReportName, acViewPreview
Set rpt = Reports(strReportName)
rpt.OrderByOn = True
rpt.OrderBy = strFieldName
cmdPrint_ClickExit:
Exit Sub
cmdPrint_ClickError:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume cmdPrint_ClickExit
End Sub
My 2003 update: Instead of using the report’s record source as the combo box’s row source, make a query limited to just the fields that are suitable for sorting. If you use them all, the user might select a Memo or OLE Object field, and get an error.
