ACC: Query Sort Ignored When You Add Function to Report Footer

• Microsoft Access versions 1.1, 2.0, 7.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you base a report on a query that has a sort order defined, the sort order is retained in the report. However, if you add a calculated field to the report footer, the sort order from the query is no longer used.

CAUSE

Reports use their own internal queries. They are not designed to use a query's sort order. The fact that a query's sort order is maintained in a report without the calculated field in the report footer is by chance.

RESOLUTION

Define your report sort order in the report's Sorting And Grouping dialog box.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.1 and 2.0).

2. Create a new query based on the Orders table.

3. Add the OrderID, ShipCity, and Freight fields.

   NOTE: In versions 1.1 and 2.0, there is a space in the Order ID and in the Ship City fields.

4. For the ShipCity field, set Sort to Ascending.

5. Close and save the query as Query1.

6. Create a new report based on Query1.

7. Add each of the fields from Query1 to the report's detail section.

8. Preview the report. Notice that the report is sorted by ShipCity.

9. Add a text box to the report's footer section. Set the ControlSource property to "=Sum([Freight])" (without the quotation marks).

10. Preview the report. Notice that the report is no longer sorted by ShipCity.

REFERENCES

For more information about sorting in a report, search on the phrase "report sort," then "sorting," and then view the "Change sorting and grouping order in a report," topic, using the Find option in the Microsoft Access for Windows 95 Help menu.