Save a sort order with a table, query, form, or report

When you sort the data in a table, query, form, or report, you can save the sort order with the object. If you sort the data in one of these objects and then save the object, the sort order that is in effect when you save the object is automatically saved with the object. You can specify whether the saved sort order will be in effect when the object is next opened.

For queries and reports, you can also define a default sort order. The default sort order is applied to the data in the query or report when no other sort order is specified.

What do you want to do?


Understand saved sort orders

There are two kinds of saved sort orders:

  • Last-applied    A last-applied sort order is the sort order that was in effect when the object was last saved. Tables, queries, forms, and reports can all have last-applied sort orders.
  • Default    A default sort order is built into the design of a query or a report. A default sort order is applied when no other sort order is specified.

Last-applied sort orders

When you save a table, query, form, or report while the data is sorted, the sort order is automatically saved when you save the object. This is called a last-applied sort order. When you create a sort order, you are actually changing the design of the object.

If you want the last-applied sort order to be automatically applied the next time that you open the object, set the Order By On Load property of the object to Yes. To set this property, perform the following steps while the object is open in Design view.

  1. Press F4 to display the property sheet.

This step is unnecessary if the property sheet is already displayed.

  1. Do one of the following:
    • If the object is a table or a query, on the General tab of the property sheet, set the Order By On Load property to Yes.
    • If the object is a form or a report, on the Data tab of the property sheet, set the Order By On Load property to Yes.

 Note   You can prevent the last-applied sort order from being applied by setting the Order By On Load property to No.

Default sort orders

Because the last-applied sort order can change whenever someone sorts an object and then saves it, you may want to define a default sort order. A default sort order is part of the design of the object, and does not change when a different sort order is applied.

You can only specify a default sort order for a query or report. Although the default sort order is not overwritten by the current or last-applied sort order, it comes into effect only when the current or last-applied sort order is removed.

Top of Page Top of Page

Define the default sort order for a query

  1. Open the query in Design view.
  2. Double-click the field that you want to use to sort.

The field appears in the design grid.

  1. In the design grid, clear the box in the Show row of the field that you added.
  2. In the Sort row, specify whether you want to sort the field in ascending order (smallest values first; A-to-Z) or in descending order (largest values first; Z-to-A).
  3. If you want to sort by more than one field, add additional sort fields by repeating steps 2 through 4.

 Note   When you sort by more than one field, results are sorted initially by the first field that you specified, then by the next field that you specified, and so on. For example, if you sort by Last Name and then by Birthdate, all records with a Last Name value of Dow appear before records with a Last Name value of Stevens, regardless of the value of Birthdate. Records within each Last Name field are then sorted according to the value in the Birthdate field.

  1. Press CTRL+S to save the query.

 Note   To remove a default sort order from a query, remove the sort fields from the query design grid.

Top of Page Top of Page

Define the default sort order for a report

  1. Open the report.
  2. On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.

A new document tab appears, containing a design grid and a window in which you can select a field on which to sort.

  1. In the window, double-click the field that you want to use to sort.

The field appears in the design grid.

  1. In the design grid, in the Sort row, specify whether you want to sort the field in ascending order (smallest values first; A-to-Z) or in descending order (largest values first; Z-to-A).
  2. If you want to sort by more than one field, add additional sort fields by repeating steps 2 through 4.

 Note   When you sort by more than one field, results are sorted initially by the first field that you specified, then by the next field that you specified, and so on. For example, if you sort by Last Name and then by Birthdate, all records with a Last Name value of Dow appear before records with a Last Name value of Stevens, regardless of the value of Birthdate. Records within each Last Name field are then sorted according to the value in the Birthdate field.

  1. On the Home tab, in the Sort & Filter group, click Toggle Filter.

 Note   To remove a default sort order from a report, remove the sort fields from the design grid.

Top of Page Top of Page

 
 
Applies to:
Access 2007