Sort Order (97 sr2)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote