Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking reports fast! The easy-to-understand wizard helps you with complex tasks like calculated fields, adding subreports, customizing styles, as well as grouping and sorting. Download a free trial today!
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 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.