Asked by:
Specify sorting sequence for report outside report

Question
-
My users want an ah-hoc reporting facility. Currently there are a fixed number of reports, each of which has its own data fields and custom sort sequence. The user selects the report and this determines the selection criteria, fields to be included and the sort sequence of the report.
Now I need to make this completely customizable. They will no longer select a report, they will specify the selection criteria and the sort sequence. I will always include all data fields.
My question is how do I specify the sorting sequence (can be single or multi level sorting) for a report outside the report itself, or does this have to be passed to the report and then set in the report's open event?
Thanks.
Thursday, May 4, 2017 4:29 PM
All replies
-
Hi,
Since reports use their own sorting system, you'll have to open the report first and then apply the sorting order from the outside. For example:
DoCmd.OpenReport "ReportName", acViewPreview
Reports!ReportName.OrderBy "FieldName"
Reports!ReportName.OrderByOn = True
Hope it helps...
Thursday, May 4, 2017 4:37 PM -
How does that work if I'm ordering by multiple fields? E.g. sorting first by DepositDate and second by CheckNumber.Thursday, May 4, 2017 4:44 PM
-
Take a look at http://www.databasedev.co.uk/sorting_reports.html
Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net- Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, May 5, 2017 5:09 AM
Thursday, May 4, 2017 5:13 PM -
How does that work if I'm ordering by multiple fields? E.g. sorting first by DepositDate and second by CheckNumber.
Hi,
As you'll see in Daniel's blog, you just include all the fields you want to sort separated by a comma. For example:
Reports!ReportName.OrderBy = "Field1, Field2, Field3"
Hope it helps...
- Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, May 5, 2017 5:09 AM
Thursday, May 4, 2017 5:24 PM -
You can pass the sort order to the report via the OpenArgs mechanism, putting the following in the report's Open event procedure:
If Not IsNull(Me.OpenArgs) Then
Me.OrderBy = Me.OpenArgs
Me.OrderByOn = True
End If
To sort a contacts report by last name and then first name for instance:
DoCmd.OpenReport "rptContacts",View:=acViewPreview,OpenArgs:="LastName,FirstName"Ken Sheridan, Stafford, England
- Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff Friday, May 5, 2017 5:09 AM
Thursday, May 4, 2017 5:36 PM