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.

Answer 1

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...

Answer 2

How does that work if I'm ordering by multiple fields? E.g. sorting first by DepositDate and second by CheckNumber.

Answer 3

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

Answer 4

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...

Answer 5

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