Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Order (97 sr2)

    I have a report that I want to print ,sorted on different fields, according to the choice made by the user. There is no difference in the records returned just the field. I am sorting on. I am using this code

    DoCmd.OpenReport "Rpt: os mailing", acViewPreview, "Qry: mailed sale date"

    I have a different query for each different sort order. It is not working. The sort order does not change.

    Is there another way I should be doing it or is there something I am missing here.

    thanks
    Cathy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Order (97 sr2)

    Reports completely ignore the sort order of the table or query that acts as record source. There are two ways you can set the sort order:

    1. In the Sorting and Grouping window; this can only be done in design view. You'd have to create a separate report for each sort order - not very attractive.

    2. In VBA code. You could create a popup form from which the user can select the sort order. You'll find a description how to do this in ACC: How to Sort a Report from a Pop-Up Form; this MSKB article contains a link to download the Microsoft Access 97 Sample Reports database; this contains a working example of this technique.

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Location
    Gloucestershire, England
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Order (97 sr2)

    I have just had a similar dilema as I needed to print the same report for a number of different sort options.

    Rather than creating different reports for each instance, I created a different query (sorted according to each specific sort type), calling the same report. This was in Access 2000, so I don't know if it works in Access 97.

    It goes along the lines of:

    Dim stCriteria as string
    Select Case me![SortOrder]
    Case "A"
    stCriteria = "Qry: mailed sale date by date"
    Case "B"
    stCriteria = "Qry: mailed sale date by orderno"
    End Select

    docmd.openreport "Rpt: os mailing", acViewPreview, stCriteria

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Order (97 sr2)

    This should work in Access 97, and if you only need two or three sorting options, it is an easy solution. But if you want to offer many sorting options (for example on multiple fields, with a choice of ascending/descending), you'd have to create a lot of queries. Setting the sort order dynamically, as in the example I referred to, will be less work and less clutter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •