Sort records on text numeric or date values

Sorting data can play a big role in making a report or form effective and easy to use. For example, a product catalog that lists the products in alphabetical order or on the basis of unit price is much easier to use than one that lists the items in random order.

This topic explains how to sort records when viewing and designing a table, query, form, or report.

In this article

• Introduction

• Sort the records in a view

• Remove a sort order

• Save a sort order with an object

Introduction

You can sort the records in a table, query, form, or a report on one or more fields. With minimal design work, you and the users of your form or report can choose how the records are sorted.

For example, when you design the product catalog report, you might choose to list the products in alphabetical order. The order you choose when you design an object becomes the object's default sort order. But when viewing the query or report, each of your users can sort the records the way they want, such as by price or by supplier.

Note: A view can be sorted on any field that is displayed in the view, except on fields that contain attachments or OLE objects.

You can fine-tune your results by sorting records on more than one field. When sorting on multiple fields, it is important to identify what are known as the outermost and innermost sort fields. Designating the appropriate fields as inner and outer sort fields is necessary to get the results you want. As an example, suppose you want to sort the Contacts table on the FirstName and LastName fields. If you want the first names sorted from A to Z (or Z to A) within each last name, FirstName is the innermost field. On the other hand, if you want the last names sorted within each first name, LastName is the innermost field. Put another way, the records are sorted first (outermost) on the LastName field and then next (innermost) on the FirstName field.

1. LastName is the outermost field and FirstName is the innermost field

2. FirstName is the outermost field and LastName is the innermost field

One thing to remember when applying sort orders is that numbers, text, and special characters are sorted according to the selected language and regional settings of your computer. If the language and regional settings specified in the Access Options dialog box do not match those specified in Control Panel, the resulting sort orders might not match your expectations.

Review or change the default language or region

To review the language settings in Access, click File > Options. On the General tab under Creating databases, review or change the value in the New database sort order list box. Set the option to General if you want to use one of these languages — Afrikaans, Albanian, Arabic, Basque (Basque), Bulgarian, Belarusian, 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.

To compact the database:

• Click Database Tools > Compact and Repair Database.

To review the regional settings for your computer, see Change the Windows regional settings to modify the appearance of some data types.

Top of Page

Sort the records in a view

Note: In Access, sorting a report is slightly different from sorting a table, query, or form.

Sort a report

Sort a table, query, or form

Sort a report

1. Open a report in Layout view or Design view.

2. If the Group, Sort, and Total pane is not open, in the Grouping & Totals group (on the Format tab in Layout view, or on the Design tab in Design view), click Group & Sort.

3. Do one of the following:

   • If the report is neither grouped nor sorted, click Add a group in the Group, Sort, and Total pane, and then select the outermost sort field. Click the second drop-down arrow and choose how the values should be ordered. Click More, then click the arrow to the right of with a header section, and select without a header section. Repeat these steps for each sort field, with the innermost sort field appearing at the bottom.

   • If the report is already grouped or sorted, review the existing levels and make changes carefully. To change a field, use the first drop-down arrow. To change the order, click the second drop-down arrow. To change a grouping level to just a sorting level, change with a header section to without a header section.

Tip: To sort a report on a single field, open the report in Layout view, right-click the field, and select the sort command you want. For example, if you select the Age field, click either Sort Smallest to Largest or Sort Largest to Smallest. But if the report is grouped or if you want to sort on multiple fields, you must use the Group, Sort, and Total pane.

Sort a table, query, or form

1. Identify the fields on which you want to sort. To sort on two or more fields, identify the fields that will act as the innermost and outermost sort fields.

2. Right-click anywhere in the column or control corresponding to the innermost field, and click one of the sort commands. The commands vary with the type of data that is in the selected field.

   Note: When sorting data for a field that is of the Yes/No data type, a value of "Yes", "True" or "On" is considered "Selected", and a value of "No", "False" or "Off" is considered "Cleared". The default display for this type of field is a check box, but you can set it to display as a text box or a combo box. If you change the display for this type of field to text box or combo box, it still sorts as "Selected" or "Cleared".

   

   1. Number, Currency, AutoNumber

   2. Short Text (text), Long Text (Memo), Hyperlink

   3. Yes/No

   4. Date/Time

3. Repeat the previous step for each sort field, ending with the outermost sort field.

   The records are rearranged to match the sort order.

   Note: 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.

   Learn about how special characters, leading spaces, and negative values are handled during sorting

   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 =.

     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, such as a minus sign, or with the same number of special characters — this way, you know how many characters to ignore. For step-by-step information about how to sort on partial values, see the article Sort records based on partial values in a field. If the number of characters to ignore varies, you can specify a custom sort order. For step-by-step information, see the article Sort records in custom order.

Remember that you cannot remove a sort order from just a single field. To remove sorting from all sort fields, on the Home tab, in the Sort & Filter group, click Clear All Sorts, and then apply the sort orders you want.

Additional sorting scenarios    

When you use the sort commands, the data type of the field and the complete values of each field are used to determine the order of records. But what if you want text values, such as Monday, Tuesday, and so on, sorted in custom order and not in alphabetical order? Or, you want to sort a text field that contains IP addresses? See the following topics if your sorting needs are not met by the sort commands:

• Sort records based on partial values in a field Want to sort records based on the first few or last few characters in a field? If you are comfortable writing simple expressions, see this topic that explains how to use expressions to extract partial values.

• Sort records in case-sensitive order Access ignores the case of text values. For example, the values smith and Smith will be treated as exactly the same, and you cannot control which one will appear first. See this topic if you want to do case-sensitive sorting.

• Sort records on numeric values stored in a text field Do you see records sorted on the individual digits and not on the numeric value? If, for example, records with values 11 and 121 appear before records with values 2 or 25, see this article.

• Sort records in custom order If you want to sort certain values, such as names of days (Monday, Tuesday, and so on) or employee titles (Manager, Director) in an order that is meaningful to you, see this article.

• Sort IP addresses A field that stores IP addresses contains multiple numbers and periods (.), and so a simple text or numeric sort will not work for IP addresses. See this topic to learn how to sort IP addresses.

Top of Page

Remove a sort order

• To remove a sort order from a table, query, or form, on the Home tab, in the Sort & Filter group, click Clear All Sorts.

  This will remove the sort order from all fields in the view.

• To remove a sort order from a report, open the Group, Sort, and Total pane by clicking Group & Sort in the Grouping & Totals group (on the Format tab in Layout view, on the Design tab in Design view). Then, click the sorting level that you want to remove, and click Delete. An entry corresponding to a sort field will start with the words Sort by. Avoid deleting entries that start with Group by, because that will change the design of the report.

Top of Page

Save a sort order with an object

The last-applied sort order is automatically saved with the table, query, form, or report. If you want it automatically applied the next time you open the object, make sure the OrderByOnLoad property of the object is set to Yes.

Since the last-applied sort order will change from time to time, you might want to define a permanent (or default) sort order. You can specify a default sort order for a query or report. An important thing to note is that although the default sort order is not overwritten by the current or last-saved sort order, it comes into effect only when the current or last-saved sort order is removed from the view.

• To define a default sort order for a query, open it in Design view. Add the sort fields to the design grid and, in the Sort row, specify the order you want.

• To define a default sort order for report, use the Group, Sort, and Total pane in Layout or Design view. For step-by-step information, see the section Sort a report.

To see the records sorted in default sort order, on the Home tab, in the Sort & Filter group, click Clear All Sorts. This removes the current or last-saved filter from the view, and lets you view the records in default order.

Top of Page