Sort records

This topic discusses the techniques used in Access for sorting records in a table, query, form, report, or data access page. It does not discuss sorting in other Microsoft Office programs, such as Excel or Outlook. For help with sorting in these programs, see the following Help topics:

For information about sorting data in a Microsoft Works database, see the Help for Works.


Hands sorting metal machine parts

Contents

Sorting records in Access
If you are new to sorting in Access, this section explains the basics.

Step-by-step procedures for sorting records
This section contains the how-to information for applying a variety of sort orders in a table, query, form, report, and data access page.

Save and reapply a sort order
This section explains when sort orders are saved and reapplied. The section also explains the differences between saved sort orders, default sort orders, and inherited sort orders.

Tasks related to sorting
This section explains how to do related tasks, such as understanding how records are sorted in a view, removing a sort order, and so on. This section also covers how to sort items in list and combo boxes, how to identify and remove duplicate records, and more.

More resources
See this section for keyboard shortcuts, information on troubleshooting sorted views, and pointers to resources that can get you started with sorting data programmatically.


Sorting records in Access

Sorting is a technique that you can use to rearrange records in a way that makes it easier for you to understand your data. When you sort the records in an object, such as a table or form, Access displays them in the order that you want.

For example, the Employees table in the Northwind Traders sample database contains nine records. When you open the table in Datasheet view, you see the records in the order in which they were originally entered.

Employee records in Datasheet view in original order

There will be times when you want to view the records in a different order. For example, you might want to see the names of employees whose birthdays fall during the current month. Or you might want to see the names of the three most recently hired employees. To see the records in a particular order, you need to do two things: Identify the fields whose values you want to determine the order of the records displayed, and apply a sort command.

The following illustration shows the Employees table sorted on the BirthDate field. The position of each record is determined based on the value in the BirthDate field. Note that even though you sort on a single field, Access shuffles entire records, not just the values in the sort column.

Employee records in Datasheet view sorted in ascending order of BirthDate field

Sometimes, you might want to sort records based on more than one field. For example, you might want to see, for each city where employees are based, the employee who has been with the company the longest. To see this, you would sort the records in the Employee table by the City field, and within each city, by the HireDate field, as shown.

Employee records in Datasheet view sorted by City and HireDate fields

Sorting in Access is easy. When combined with filtering or grouping, it can cut down the amount of scanning and scrolling that you need to do to analyze large volumes of data.

This topic describes sorting records in an Access database. To learn more about sorting records in an Access project, see the article Sort records in an Access project (ADP).

To learn more about filtering (filter: A set of criteria applied to data in order to display a subset of the data or to sort the data. In Access, you can use filtering techniques, such as Filter By Selection and Filter By Form, to filter data.) records, see the Access Help topic Filter: Limit the number of records in a view or report.

Back to top  Back to top

Step-by-step procedures for sorting records

This section provides step-by-step procedures for applying sort orders in each of the views in an Access database.

ShowBefore you start, review the database and computer settings that affect sorting

Two settings — the language that has been chosen as the default for a database and the regional settings specified in Control Panel as the default for your computer — influence the sort orders that you apply. Numbers, text, and special characters will be sorted according to the selected language and regional settings. If your current language or region is different from the selected ones, the resulting sort orders might not match your expectations.

Review or set the default language for sorting data in a database     On the Tools menu, click Options. On the General tab, review or change the value in the New database sort order box. Set the option to General if you want to use one of these languages — Afrikaans, Albanian, Arabic, Basque, Bulgarian, Byelorussian, Catalan, Dutch, English, Faeroese, Farsi, German-Standard, Greek, Hebrew, Hindi, Indonesian, Italian, Malay, Portuguese, Russian, Serbian, Swahili, and Urdu. Note that this setting affects only new databases. To apply this setting to an existing database, first compact the database. For more information, see the Access Help topic Compact and repair an Access file.

Review or change the regional settings in Control Panel     The order in which numbers, text, and special characters are sorted depends on the settings in Regional and Language Options in Control Panel. For more information, see Microsoft Windows Help.


See the following sections for step-by-step procedures for sorting records.

Sort records in Datasheet or Form view

Sort records in a report

Sort records in Page view

Sort records in PivotTable or PivotChart view

Specify a sort order in Design view

Sort records in Datasheet or Form view

Make sure the column or control corresponding to the field that you want to sort on is visible in the view. To sort records in a subdatasheet (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.), display the subdatasheet by clicking its expand indicator (+), and then click the field you want to sort on.

ShowShow a hidden field to use for sorting records in Datasheet view

If a sort field is hidden, on the Format menu, click Unhide Columns. In the Unhide Columns dialog box, select the names of the columns that you want to show.


The step-by-step procedures vary with the number of fields that you want to sort on and the data type of each sort field. The procedure Sort records based on two or more fields follows the procedures that explain sorting on a single field.

Sort records based on a text, memo, or hyperlink field    

Note that memo fields are sorted only on the first 255 characters.

ShowSort records alphabetically based on the first character of the field values

  • Click anywhere in the field, and then click Sort Ascending Button image or Sort Descending Button image on the toolbar.

An ascending sort in alphabetical order displays records starting with "A" or 0 on top and those starting with "Z" or 9 at the bottom. A descending sort displays records starting with "Z" or 9 on top and those starting with "A" or 0 at the bottom.


ShowSort records alphabetically based on partial values in a text, memo, or hyperlink field

Sometimes, you might not want to sort based on the whole string in a field. Instead, you might want to ignore the first few letters or ignore the first word and use the remaining string to sort the records.

For example, consider a field that contains values such as North Seattle, South Seattle, North Tacoma, or South Tacoma. If you want to ignore the first word and sort on the name of the city alone, use the Advanced Filter/Sort dialog box. In this dialog box, you specify an expression that extracts the partial values that must be used to sort records.

  1. On the Records menu, point to Filter, and then click Advanced Filter/Sort.
  2. Type an expression in the Field row in the first column. For example, type =Mid(Location, 7) to ignore the first six characters in the Location field. Then click Apply Filter Button image.

Sorting on partial values using Advanced Filter/Sort dialog box

Mid is a function that returns a substring contained in a specified string or field. In this case, for each record, the function returns the value starting from the seventh character (that is, after the space) in the Location field. After applying the filter, you can then click Sort Ascending Button image to further sort the records, so that, for example, records with "North Seattle" will be followed by records with "South Seattle," and then "North Tacoma," and finally "South Tacoma."

ShowLearn more about using expressions and functions when sorting records

An expression is like a formula that returns a value for each record. Access uses the return values to determine the sort order of the records. All expressions are a combination of functions, operators, field names, and constants. To learn how to write expressions, and for more information about operators and functions available in Access, see the Access Help topics Create an expression and Functions (by category).

If you need help with writing a complex expression, or if an expression is not working as expected, you can get help from communities on the Web by posting a question to Access experts and other users. Learn about communities with Ask the Access community. If you want to learn more about posting questions to communities, see the article Start getting answers from other Office users.

  1. In the Sort cell, select Ascending or Descending.

An ascending sort displays records starting with "A" or 0 on top and records starting with "Z" or 9 at the bottom. A descending sort displays records starting with "Z" or 9 on top and records starting with "A" or 0 at the bottom.

  1. Click Apply Filter Button image on the toolbar to sort the records.

ShowSort records in nonalphabetical (custom) order based on text values

Though text values are often sorted in alphabetical order, there are cases where a nonalphabetical order makes more sense. Examples include:

  • Sorting on names of days, such as Monday, Tuesday, Wednesday, and so on.
  • Sorting on grades, such as A+, A, A-, B+, and so on.
  • Sorting on titles, such as CEO, Sr Vice President, or Vice President.

As you can see, the order in the preceding examples is user-defined. The first step in sorting records in custom order is to define the order in Access. You define the order by assigning a rank to each value, and Access then uses the rankings to sort the records in the order that you want. For more information about how to assign rankings to field values and to sort records based on these rankings, see the Access Help topic Sort records in custom order.


ShowSort records based on a field that starts with special characters and spaces

If a value in a field starts with a special character — such as a hyphen, parenthesis, or other symbol — you will observe the following behavior when sorting in ascending order:

  • Values that start with a space character will appear before alphanumeric values.
  • Values enclosed in quotation marks follow values that start with a space, but precede alphanumeric values.
  • Values that start with the minus sign precede values that start with the plus sign.
  • For all other symbols, the order is determined by looking at the ASCII character codes of the characters. For example, the code for the dollar symbol ($) is 36, and the code for the equal sign (=) is 61, so values starting with $ will appear before values that start with =.

For a full list of ASCII character codes, see the Microsoft Developer Network (MSDN) article ASCII Character Codes.

To override this order, you can choose to ignore the first character of all the values in the field. This technique works if the values in the field always start with a single special character or with the same number of special characters, so you know how many characters to ignore. For step-by-step information on how to sort on partial values, see the section Sort records based on partial field values. If the number of characters to ignore varies, you can specify a custom sort order. For step-by-step information, see the section Sort records in custom order.


ShowSort records based on a field that contains null and zero-length strings

If a text field contains null and zero-length strings, when sorted in ascending order, the records with null values are listed first, then the records with zero-length strings, and then the records with nonblank values.

If you don't want to see the blank values at the top, you can sort the field in descending order, but note that the nonblank values will also be listed in descending order. If you have a large number of records with blank values, or you are interested only in records with nonblank values, you can apply a filter to hide records with blank values and then sort the remaining records in ascending order. For step-by-step information about hiding records with null and blank values, see the section "Examples of filters" in the Access Help topic Filter: Limit the number of records in a view or report. The section also has examples of filter expressions you can use to hide records that contain null and blank values.


ShowSort records based on a field that contains lowercase and uppercase values

When sorting, Access ignores the case of the field values. If it encounters two identical values — one uppercase and the other lowercase — Access lists them in the order in which they were entered. For example, if a record with the value "smith" in the LastName field was entered before a record with the value "Smith" in the same field, the record containing "smith" will be displayed before the record containing "Smith".

To do case-sensitive sorting, see the article How to sort records in case-sensitive (ASCII) order. This article assumes that you are familiar with writing code using Visual Basic for Applications (VBA).


ShowSort records based on a field that contains both text and numeric values

Sort records based on a number, autonumber, or currency field    

ShowSort records based on numeric values

  • Click anywhere in the field, and then click Sort Ascending Button image or Sort Descending Button image on the toolbar.

Performing an ascending sort displays the record with the smallest value at the top, and a descending sort displays the record with the largest value at the top. Records containing a negative value in the field will be displayed before records with a positive value when they are sorted in ascending order.


ShowTreat negative and positive values the same during sorting

Records with negative values will appear before records with positive values, but if you want to sort only on the numeric portion of the values, you need to use the Advanced Filter/Sort dialog box. In the window, specify an expression that will ignore the minus sign in the field. Though a negative value appears enclosed in parentheses in a Currency field — for example, ($9.95) — keep in mind that the value is stored in the database with a minus sign. Thus, an expression that works by ignoring the minus sign will work for both Number and Currency fields.

  1. On the Records menu, point to Filter, and then click Advanced Filter/Sort.
  2. Type the expression Expr1: IIf(Left([MyNumber],1)="-",Mid([MyNumber],2),[MyNumber]) in the Field row in the first column.

Sorting on numeric portion of Number or Currency field

For each record, the expression first uses the IIf function to determine if there is a minus sign in the field; if there is, the Mid function returns only the characters after the minus sign. This allows Access to sort the field on positive numbers only.

ShowLearn more about using expressions and functions when sorting records

An expression is like a formula that returns a value for each record. Access uses the return values to determine the sort order of the records. While the expression that you will use will vary with your requirements, all expressions are a combination of functions, operators, field names, and constants. To learn how to write expressions and what operators and functions are available in Access, see the Access Help topics Create an expression and Functions (by category).

If you need help with writing a complex expression, or if an expression is not working as expected, you can get help from communities on the Web by posting a question to Access experts and other users. Learn about communities with Ask the Access community. If you want to learn more about posting questions to communities, see the article Start getting answers from other Office users.

  1. In the Sort cell, select Ascending or Descending.

An ascending sort displays the record with the smallest value on top and the record with the largest value at the bottom. A descending sort does the opposite.

  1. Click Apply Filter Button image on the toolbar to sort the records.

If ignoring the minus sign makes two values (such as -3 and 3) the same, Access lists the records in the order in which they were originally entered into the database.


ShowSort numbers stored in a nonnumeric field

Numbers stored in a text or memo field will be sorted alphabetically, that is, based on the first character, instead of on the numeric value. For example, the value 11 will appear before 2, and the value 2000 will appear before 3.

To avoid having numbers treated as text values during sorting, do one of the following:

  • If the field contains only numeric values, consider changing the data type of the field to Number or Currency.
  • If the field contains only numeric values, but you do not want to change the data type of the field, or you want to ignore the nonnumeric characters when sorting, use an expression that calls the Val function in the Advanced Filter/Sort dialog box. If the field contains null values, use the IIf function to treat null values as 0 or some other number.
    1. On the Records menu, point to Filter and then click Advanced Filter/Sort.
    2. Type the expression Expr1: IIf([Fieldname] Is Null, 0, Val([Fieldname])) in the Field row in the first column. The IIf function checks to see if the field value is null, and if so, treats the value as a 0. If the value is not null, it calls the Val function to obtain the numeric equivalent.

ShowLearn more about using expressions and functions when sorting records

An expression is like a formula that returns a value for each record. Access uses the return values to determine the sort order of the records. While the expression that you use will vary with your requirements, all expressions are a combination of functions, operators, field names, and constants. To learn how to write expressions and what operators and functions are available in Access, see the Access Help topics Create an expression and Functions (by category).

If you need help with writing a complex expression or if an expression is not working as expected, you can get help from communities on the Web by posting a question to Access experts and other users. Learn about communities with Ask the Access community. If you want to learn more about posting questions to communities, see the article Start getting answers from other Office users.

  1. In the Sort cell, select Ascending or Descending.

An ascending sort displays the record with the smallest value on top and the record with the largest value at the bottom. A descending sort does the opposite.

  1. Click Apply Filter Button image on the toolbar to sort the records.
  • If the previous two techniques do not work for you, make sure the values are of the same length before sorting the records. You can do this by padding values that contain fewer digits with leading zeroes. For example, the values 11, 2, 2000, and 3 will need to be changed to 0011, 0002, 2000, and 0003 before you apply the sort. For more information about how to pad values with leading zeroes, see the Microsoft Knowledge Base article How to pad character strings on left and right side. This Knowledge Base article assumes you are familiar with writing code using Visual Basic for Applications (VBA).

ShowReset the values in an AutoNumber field that is used for sorting

An AutoNumber field automatically gets a value at the time you add the record. You cannot change the value in an AutoNumber field.

When you delete records, the values in the AutoNumber field of the deleted records are lost and do not get assigned to the existing records. Over time, this leads to gaps in the values in the AutoNumber field. If you often sort on this AutoNumber field and want to reset the numbers in the field, see the Knowledge Base article How to reset an AutoNumber field value in Access.


Sort records based on a date/time field    

ShowSort records chronologically (by using the complete date or time values in the field)

  • Click anywhere in the field, and then click Sort Ascending Button image or Sort Descending Button image on the toolbar.

An ascending sort displays the oldest date or time at the top and the most recent date or time at the bottom, and a descending sort displays the most recent date or time at the top and the oldest date or time at the bottom.


ShowIgnore the year or month part of a date/time field when sorting

Sometimes, you might want to sort only on the month or day portion of a date field. For example, to see all the birthdays that occur in a specific month, you need to look only at the month and ignore the year part of the date value. Similarly, you might want to ignore the minutes or seconds portion of a field that contains time values.

To sort on partial values, type an expression that extracts a portion of the field values in the Advanced Filter/Sort dialog box.

  1. On the Records menu, point to Filter and then click Advanced Filter/Sort.
  2. Type an expression in the Field row in the first column. For example, to sort by months irrespective of the years, type the expression Expr1: DatePart("m",[BirthDate]) in the Field row in the first column.

To sort records by days within each month, type Expr2: DatePart("d",[BirthDate]) in the Field row in the second column.

Sorting on partial date or time values

The DatePart function in the first column sorts the records by month, and the DatePart function in the second column sorts the records by days within each month.

ShowLearn more about using expressions and functions when sorting records

An expression is like a formula that returns a value for each record. Access uses the return values to determine the sort order of the records. All expressions are a combination of functions, operators, field names, and constants. To learn how to write expressions and what operators and functions are available in Access, see the Access Help topics Create an expression and Functions (by category).

If you need help with writing a complex expression, or if an expression is not working as expected, you can get help from communities on the Web by posting a question to Access experts and other users. Learn about communities with Ask the Access community. If you want to learn more about posting questions to communities, see the article Start getting answers from other Office users.

  1. In the Sort cell, select Ascending or Descending for each of the expressions.

An ascending sort in the first column displays January at the top and December at the bottom. An ascending sort in the second column displays 1 at the top and 31 at the bottom. A descending sort does the opposite.

  1. Click Apply Filter Button image on the toolbar to sort the records.

Sort records based on IP addresses    

See the topic Sort IP addresses in an Access database.

Sort records based on a Yes/No field    

  • Press TAB until the field has focus and then click Sort Ascending Button image or Sort Descending Button image on the toolbar.

An ascending sort displays values where the check box is selected at the top and values where the check box is cleared at the bottom. A descending sort does the opposite.

Note that a distinction is made between records with null values and those records where the check box was explicitly cleared. When sorted in ascending order, records with null values appear before records with cleared check boxes.

Sort records based on calculated fields or columns    

You cannot sort based on a calculated column by using the sort buttons on the toolbar. However, you can sort based on an expression by using the Advanced Filter/Sort dialog box.

  1. On the Records menu, point to Filter and then click Advanced Filter/Sort.
  2. Type the expression behind the calculated column or control. You can also copy the expression behind the calculated column or control and paste it in the Field row.

To find the expression behind a calculated column in a query, open the query in Design view, and look for the expression in the design grid. To find the expression behind a calculated control in a form, open the form in Design view, select the control, press F4, and then look in the ControlSource property box in the property sheet.

  1. In the Sort cell, select Ascending or Descending.

The order of individual values depends on the type of value that results from evaluating the expression. For example, if the expression returns numeric values, you will see the smallest value at the top when sorting in ascending order and the largest value at the top when sorting in descending order.

  1. Click Apply Filter Button image on the toolbar to sort the records.

Sort records based on two or more fields    

  • To sort on adjacent columns in a datasheet, select two or more adjacent columns at the same time, and click one of the sort buttons.

Adjacent columns selected for sorting

Access treats the leftmost column as the outermost sort column, the second right column as the inner sort column, and so on. For example, if you select the LastName and FirstName columns, and if the LastName column is to the left of the FirstName column, then, when you do an ascending sort, you will see the last names in ascending order, and within each last name, the first names will be listed in ascending order.

Employees table sorted on LastName and FirstName fields

  • If you want to sort on multiple fields in Form view, or sort on nonadjacent columns in Datasheet view, or specify a column order other than the default left-to-right order in Datasheet view, use the Advanced Filter/Sort dialog box.

Note that you cannot sort on multiple fields in Form view by using the Sort Ascending or Sort Descending button on the toolbar.

  1. On the Records menu, point to Filter and then click Advanced Filter/Sort.
  2. If the fields on which you want to sort are not in the design grid, add them to the Field row. If you want to sort records based on calculated values, type an expression in the Field cell.

Tip    Just prior to this procedure, you will find several step-by-step procedures that cover sorting on fields of different data types, and on partial field values and expressions to handle special cases. You might want to review these procedures before attempting to sort on multiple fields.

Any column that has the value Ascending or Descending in the Sort row will impact the order of rows. The column positions of these sort fields or expressions in the design grid are important. The leftmost sort column acts as the outermost sort field, the field in the second left field acts as the inner sort field, and so on. For example, if you specify LastName in the first column, and FirstName in the second column, you will see first names sorted from A to Z or Z to A within each last name, as shown in the following illustration.

Sorting datasheet on multiple columns

If you change the order of the fields in the design grid, you will see last names sorted from A to Z or Z to A within each first name, as shown in the following illustration.

Sort datasheet on FirstName and LastName fields

Note that the position of the fields or columns in the Datasheet view does not affect the sort order.

  1. For each field on which you want to sort, specify the sort order. In the Sort row, select Ascending or Descending. You can select a different order for each field.
  2. Click Apply Filter Button image on the toolbar to sort the records.

Sort records in a report

You can apply a sort order to a report in Design view, but not in Print Preview or Layout Preview.

You can sort the groups and records within each group in a report. When you choose to sort records inside a group, the sort order applies to the individual records of all groups at that level.

You sort a report containing mailing labels the same way you sort any other report.

  1. Open a report in Design view.
  2. Click Sorting and Grouping Button image on the toolbar to display the Sorting and Grouping dialog box.

Sorting and Grouping dialog box

  1. Specify the sort fields or expressions. To sort on a single field or expression, in the first row of the Field/Expression column, select a field name or type an expression.

Tip    The section Sort records in Datasheet or Form view includes several step-by-step procedures that cover sorting on fields of different data types, and on partial field values and expressions to handle special cases. You might want to review these procedures if you are not sure what to specify in the Field/Expression column.

To sort on more than one field or expression, specify the outermost sort field in the first row, the next sort field in the second row, and so on. For example, to sort on last names, and within each last name, to sort records by first name, specify the LastName field in the first row and the FirstName field in the second row.

  1. For each sort field or expression, select Ascending or Descending in the Sort Order column. The following illustration shows the sort settings for a report that groups sales data by country/region, and within each country/region, by salesperson. The individual records for each salesperson are sorted in ascending order of the OrderID field.

Sorting and Grouping dialog box for Employee Sales by Country report

Note that when you specify a field that is used as the basis for grouping records, in the Field/Expression column, the report displays the groups in sorted order. To sort the individual records inside the groups in a specific order, you need to specify one or more fields in the rows below.

You can also specify a sort order in the OrderBy property of a report. Unlike the sort order defined in the Sorting and Grouping dialog box, the order specified in the OrderBy property acts as the default sort order and is in effect only when a sort order is not defined in the Sorting and Grouping dialog box. If you want to define a default sort order for a report, see the section Specify a default sort order for a report.

Sort records in Page view

Note that in Page view, you cannot sort on partial values, nor can you sort based on expressions. Also, you cannot sort on Hyperlink and Memo fields.

  1. Open a data access page in Page view.
  2. Do one of the following:
    • On an ungrouped page, click the field on which you want to sort records.
    • On a grouped page, click the group field to sort the groups.
    • On a grouped page, to sort the individual records inside a group, expand the group, and then click the field on which you want to sort records. Note that unlike in a report, the sort order only applies to the current group. To sort the individual records in all the groups, you must repeat the step for each group.
  3. Click Sort Ascending Button image or Sort Descending Button image on the record navigation toolbar.

Sort records in PivotTable or PivotChart view

In PivotTable or PivotChart view, you can sort records in ascending or descending order, and also in custom order, based on the values in one or more fields. For step-by-step information, see the Access Help topic Sort records in a PivotTable or PivotChart view.

Specifying a sort order in Design view

You can specify a sort order for a query, form, report, and page in Design view. Note that you cannot sort a report in any other view. Also, you cannot specify a sort order for a table in Design view.

Queries     When you specify a sort order for a query in Design view, it becomes the default sort order of the object. The default sort order settings do not get overwritten when you apply a sort order in Datasheet view. However, if at the time the query was last saved it was sorted in an order other than the default order, the next time you open the query, you will see records sorted in saved order. In other words, when you open a query, you will see records sorted in default order only if the query was not saved with the sort order that was last applied in Datasheet view. For step-by-step information about specifying a default sort order for a query, see the section Specify a default sort order for a query.

Reports     You can specify a sort order for a report in two ways in Design view — either by setting the OrderBy and OrderByOn properties of a simple (ungrouped) report or by using the Sorting and Grouping dialog box for both simple and grouped reports. The sort order specified by using the report properties becomes the default sort order. The default sort order will be applied to a simple report only when a different sort order is not specified in the Sorting and Grouping dialog box. For step-by-step information about specifying a default sort order for a simple report, see the section Specify a default sort order for a report. For step-by-step information about specifying a sort order by using the Sorting and Grouping dialog box, see the section Sort records in a report.

Forms     You can specify a sort order for a form in Design view by setting the form's OrderBy property the same way that you set a report's OrderBy property. However, the sort order will be replaced when you apply a different sort order in Form view or Datasheet view. When working with forms, a distinction is not made between default sort orders and saved sort orders. For more information about how sort orders are saved, see the section Save a sort order.

Pages    You can specify a default sort order for simple and grouped pages in Design view. When working with a grouped page, you can specify a sort order for each group level. For step-by-step information about specifying a default sort order for a page, see the section Specify a default sort order for a page. Note that sort orders that are applied in Page view are not saved, so a page always displays the records in default sort order, if one is specified.

Back to top  Back to top

Save and reapply a sort order

Often, you might want to save an object with the records sorted in a specific order, so that you don't have to sort the records each time you open an object.

In Access, you can do this in the following ways.

Save the last applied sort order , and have Access automatically apply it the next time you open the object

When you save an object, such as a table or form, the current sort order is automatically saved with the object. The only exception to this is that Access does not save sort orders applied in Page view. To save a sort order in a page, see the next section, Save a specific sort order as the default for an object.

The sort order that is saved with an object is referred to as the "saved sort order." A saved sort order is automatically applied when you open the object the next time. For example, if you applied a descending sort to the Employees table based on the Employee ID field before saving and closing the table, the next time you open it, you will see the records sorted in descending order of Employee IDs.

To review the saved sort order settings of a table, query, or form, open the Advanced Filter/Sort dialog box. To review the saved sort order settings of a report, look in the Sorting and Grouping dialog box. For step-by-step information, see the section Review the sort order settings.

Save a specific sort order as the default for an object

As explained in the previous section, the sort order that is saved with the object will likely change each time you save an object. If you want to permanently save a specific order with an object, you can do so in Design view. Also, when working with data access pages, you can save only sort orders that are specified in Design view.

In Design view, you can specify a default sort order for a query, a simple report, or a page. For a grouped page, you can specify the default sort order for each group level.

 Note   You cannot specify a default sort order for a table, a form, or grouped reports.

The important thing to note is that queries and simple reports support both default and saved sort orders, but the saved sort order will always override the default order. For example, if a query has a default order specified in Design view but was saved with a different sort order, when you open the query the next time, you will see records sorted in the saved sort order. If you want the object to be sorted in the default sort order, all you need to do is remove the saved sort order. For step-by-step information about removing a saved sort order to view records in the default sort order, see the section Remove a saved sort order.

ShowSpecify a default sort order for a query

  1. Open a query in Design view.
  2. In query Design view, if the sort fields are not already in the design grid, add them.

Note that if the design grid contains only the asterisk (*) from the field list, add the sort fields starting from the second column. If you want to sort on a calculated column, write the expression behind the calculated column in the Field row in a blank column in the grid.

Place the sort fields in the order in which you want the sorts performed. Access sorts on the leftmost field first, then on the second field, and so on. For example, to sort on last names first, and within each last name, by first names, the LastName field must be in the first column and the FirstName field must be in the second column.

  1. In the Sort row, click Ascending or Descending for each sort field.
  2. Click Save on the toolbar.
  3. To see the default sort order in effect, remove the current or saved sort order, and then save, close, and reopen the query.

ShowSpecify a default sort order for a simple report

  1. Open a report in Design view.
  2. Double-click the section selector to display the report's property sheet.
  3. In the OrderBy property box, type an expression that lists the sort fields and sort order for each field. For example, to sort on the LastName field in ascending order, type LastName Asc. To sort on last names in descending order, and then on first names in ascending order, type LastName Desc, FirstName Asc. Note that if you don't specify "Asc" or "Desc" next to a field, Access assumes an ascending sort order for the field.

If the control that you want to sort on is bound to an expression, you must repeat the expression in the OrderBy property box. For example, if the ControlSource property of the control that you want to sort on is set to [FirstName]&[LastName], you must type [FirstName]&[LastName] Asc in the OrderBy property box to sort on the combined string.

  1. Set the OrderByOn property to Yes.
  2. Click Save on the toolbar.
  3. To see the default sort order in effect, remove the current sort order, and then close and reopen the report.

ShowSpecify a default sort order for a page

  1. Open a page in Design view.
  2. Scroll to a section that belongs to the group for which you want to specify the sort order.
  3. Right-click the section bar, and then click Group Level Properties.
  4. In the DefaultSort property box, type an expression that lists the sort fields and sort order for each field. For example, to sort on the LastName field in ascending order, type LastName Asc. To sort on last names in descending order and then on first names in ascending order, type LastName Desc, FirstName Asc. Note that if you don't specify "Asc" or "Desc" next to a field, Access assumes an ascending sort order for the field.

If the control that you want to sort on is bound to an expression, you must type the alias of the control as displayed in its ControlSource property. For example, if the ControlSource property of the control you want to sort on is set to GroupOfExtendedPrice: ExtendedPrice, type GroupOfExtendedPrice in the DefaultSort property box.

  1. Set the DefaultSort property for each group level that you want sorted.
  2. Click Save on the toolbar.
  3. To see the default sort order in effect, close and reopen the page.

Save the current sort order of a table or query with a new form or report

When you create a form or report based on a table or query, the new object automatically inherits the sort order of the underlying table or query. If you create the object based on an open table or query, the current sort order of the table or query is inherited by the new object. If the table or query is not open when you create the object, the saved sort order is inherited by the new object.

 Note   This technique works only when the form or report is based on a single table or query. If the object includes fields from more than one table or query, you need to specify the sort order after creating the object.

If the table or query is open, make sure it is sorted in the order that you want the new object to inherit. Then click the arrow of the New Object button on the toolbar, and click AutoForm or AutoReport. If the table or query is not open, create the form or report as usual. The new object will automatically inherit the order that is saved with the table or query.

The inherited sort order is also set as the default order for the form or report. If there is a saved sort order, the OrderBy property of the form or report contains the sort expression, and the OrderByOn property of the report is set to Yes. The only exception is if the query is sorted in default order (as specified in query Design view) — the query's default order is not set as the default order of the new object. In this case, the OrderBy property of the form or report is left blank, and when working with a report, the OrderByOn property is set to No.

Note that when working with a form, the default sort order is lost when you apply a different sort order. This is because both the default sort order and the saved sort order are stored in the OrderBy property of the form. However, for a report, a sort order applied by using the Sorting and Grouping dialog box does not affect the OrderBy and OrderByOn properties.

The following table summarizes the types of sort orders that are supported by each object and where to look for the sort order details. Note that the saved sort order always overrides the default and inherited sort orders.

Object Supports saving a sort order? Supports specifying a default sort order? Inherits sort order?
Table Yes, look in the Advanced Filter/Sort dialog box for sort details. No NA
Query Yes, look in the Advanced Filter/Sort dialog box for sort details. Yes, view the query design grid for sort details. NA
Form Yes, look in the Advanced Filter/Sort dialog box or the OrderBy property setting for sort details. No Yes
Report Yes, look in the Sorting and Grouping dialog box for sort details. Yes, review OrderBy and OrderByOn property settings for sort details. Note that grouped reports do not support default sort orders. Yes
Page No Yes, review the DefaultSort property of a group level for sort details. No

Back to top  Back to top

Tasks related to sorting

Review the sort order settings

To understand how records are sorted, review the saved and default sort order settings of the object, in that order.

ShowReview the saved sort order settings of a table, query, or form

  1. On the Records menu, point to Filter and then click Advanced Filter/Sort.
  2. Review the Sort row of each column in the grid. Any column that has a value in the Sort row acts as a sort column and affects the sort order of the records. Note that if the Sort row is blank for all columns, the object does not have a saved sort order. Review the default sort order settings to understand how the records are sorted.
  3. Review the field name or expression in the Field row of each sort column. The order of the sort columns in the grid is important. The leftmost sort column acts as the outermost sort field, the field in the second left column acts as the inner sort field, and so on. For example, if the LastName field is specified in the first column and the FirstName field is specified in the second column, the records will first be sorted by last name and then by first name.

If the Field row contains an expression instead of a field name, you should review the expression.

ShowLearn more about using expressions and functions when sorting records

An expression is like a formula that returns a value for each record. Access uses the return values to determine the sort order of the records. All expressions are a combination of functions, operators, field names, and constants. To learn how expressions are written and what operators and functions can be used in expressions, see the Access Help topics Create an expression and Functions (by category).


ShowReview the saved sort order settings of a report

  1. In Design view, click Sorting and Grouping Button image on the toolbar to display the Sorting and Grouping dialog box.
  2. Review the entries in the Field/Expression column. The first row specifies the outermost sort field, the next row specifies the second sort field, and so on. For example, if the LastName field is in the first row and the FirstName field is in the second row, the records will first be sorted by last name and then by first name.

If you see an expression instead of a field name in the Field/Expression column, review the expression to see how the records are sorted.

ShowLearn more about using expressions and functions when sorting records

An expression is like a formula that returns a value for each record. Access uses the return values to determine the sort order of the records. All expressions are a combination of functions, operators, field names, and constants. To learn how expressions are written and what operators and functions can be used in expressions, see the Access Help topics Create an expression and Functions (by category).


ShowReview the default sort order of a query, simple report, or page

To review the default sort order of an object, open it in Design view.

Query    The query Design view is very similar to the Advanced Filter/Sort dialog box. Review the Field and Sort rows in the grid to understand the default sort order.

Simple report     To review the default sort order of a report that is not grouped, review the report's OrderBy and OrderByOn properties. Double-click the report selector, and review the two properties in the report's property sheet. The OrderBy property lists the sort fields or expressions separated by a comma. The string "Asc" or "Desc" that follows a field name or expression specifies either an ascending or descending sort order. If neither "Asc" nor "Desc" is mentioned, an ascending sort order is assumed. The OrderByOn property determines whether the default order will be applied or ignored. If the property is set to No, the report will not be sorted in default order until the property is set to Yes.

Page    To review the default sort order of records in a page, review the DefaultSort property of the group level. Right-click a header or footer bar that belongs to a group, and click Group Level Properties. In the property sheet, review the list of sort fields or expressions. The string "Asc" or "Desc" that follows a field name or expression specifies an ascending or descending sort order. If neither "Asc" nor "Desc" is mentioned, an ascending sort order is assumed. If the page is grouped, review the DefaultSort property of each group level.


A form or report that is based on a single table or query inherits the last-applied sort order of the underlying object, but you cannot review the details of the order.

Undo or remove a sort order

The quickest way to remove a sort order is to simply apply a different sort order. However, if you want to see the records in default sort order or in the order that they were originally entered, do one of the following:

  • To remove the current sort order in Datasheet view or Form view, on the Records menu, click Remove Filter/Sort. This removes the current or saved sort order of a table, query, or form.

 Note   Doing this will remove the current filters from the view. To remove only the sort order and retain the current filter settings, in the Advanced Filter/Sort dialog box, remove the value from the Sort row for all columns.

  • To remove the current sort order of a report, in Design view, click Sorting and Grouping on the toolbar, and delete the entries from the Field/Expression column. Note that this will remove all grouping and sorting from the report.
  • To remove the current sort order in Page view, simply close and reopen the page.

When you remove the current or saved sort order, the records will be displayed in default order, if a default order is specified. To remove a default sort order, do one of the following:

  • To remove a query's default sort order, open the query in Design view and then remove the value from the Sort row for all columns.
  • To remove a report's default or inherited sort order, open the report in Design view, double-click the report selector, and in the property sheet, set the OrderByOn property to No. Optionally, you can delete the value of the OrderBy property.
  • To remove a page's default sort order, open the page in Page view, right-click the header or footer bar of a group level, and then click Group Level Properties. In the property sheet, delete the value of the DefaultSort property. If the page is grouped, you must reset the DefaultSort property for each group level.

After you remove the saved and default sort orders from a table or query, you will see records in the order in which they were originally entered. After you remove the saved and default sort orders from a form, report, or page, you will see the records sorted in inherited order.

Refresh a sort order

When you add records to a sorted view, you will notice that the new records are not sorted automatically — they continue to be at the bottom until you reapply the sort order.

To reapply a sort order in Form view or Datasheet view, on the Records menu, point to Filter and then click Advanced Filter/Sort. Click Apply Filter on the toolbar.

To reapply a sort order in Page view, click one of the sort buttons on the Record Navigation toolbar.

To refresh the sort order in a report, close and reopen the report.

Find duplicate values and records

Find duplicate values in a field    To see if more than one record contains a specific value in a particular field, sort the records in ascending or descending order.

Find duplicate (identical) records    To find duplicate records (where the values in one or more fields in the record match those of the corresponding fields in another record), see the topic Find, eliminate, or hide duplicate records in Access.

Sort items in a list or combo box

In a new list or combo box    You can specify the sort order of up to four columns in a list or combo box by using the List Box Wizard or Combo Box Wizard in a form or report. You can also sort the lookup list of a new field in a table that looks up values in another object.

In the wizard, when you select the option to look up values in a table or query in step 1, you will have the option of specifying the sort orders in step 4. If you are manually entering the values for the control or field, make sure that you enter them in sorted order.

In an existing list or combo box    If you want to specify or change the sort order of an existing control that looks up values in a table or query, edit the RowSource property of the control. In form Design view, select the control and press F4 to display the control's property sheet. Click the RowSource property and then click the Build (...) button to the right of the property box. In the design grid, review and change the values in the Sort row. To sort on a specific field, set the field's Sort row value to Ascending or Descending. If you don't want to sort on a specific field, set the Sort row of the field to (not sorted).

Back to top  Back to top

More resources

This section describes the keyboard shortcuts that are used when sorting, contains additional information about troubleshooting sort orders, and helps you get started with using Visual Basic for Applications (VBA) code to programmatically work with sorting.

Keyboard shortcuts

The following table lists keyboard shortcuts for frequently performed actions when sorting records.

To do this... Press...
Switch between the upper and lower portions of the Advanced Filter/Sort dialog box F6
Move between columns or fields TAB or SHIFT+TAB
Move between rows in the Advanced Filter/Sort dialog box UP and DOWN ARROW keys
Sort data in the selected field or total in ascending order (A–Z 0–9) in PivotTable or PivotChart view CTRL+SHIFT+A
Sort data in the selected field or total in descending order (Z–A 9–0) in PivotTable or PivotChart view CTRL+SHIFT+Z
Display the property sheet of a form or report F4

Troubleshooting

For help with troubleshooting issues related to sorting records in Access, see the Help topic Troubleshoot sort (MDB).

Programming topics

The following Access Help topics can get you started with sorting records programmatically by using VBA code.

If you are new to writing macros or code, see the Access Help topics Create a macro and Create a Visual Basic procedure. If you need help with specific requirements or with debugging your code, you can get help from Access experts and users. For more information, see the articles Ask the Access community and Start getting answers from other Office users.

You specify a sort order for a form or report by setting the OrderBy property of a form or report, and by setting the OrderByOn property of a report programmatically. For more information about setting these properties, see the following Access VBA Help topics:

Back to top  Back to top

 
 
Applies to:
Access 2003