locked
Specify sorting sequence for report outside report RRS feed

  • 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

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

    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

    Thursday, May 4, 2017 5:36 PM