Troubleshoot sorting

This topic explains how to troubleshoot sorting in Access. It does not cover sorting in other Microsoft Office programs, such as Excel or Word. For troubleshooting help with these topics, see the following:


If you are new to sorting, see the topic Sort records to learn about sorting in Access.

If you are working with an Access project, see Sort records in an Access project (ADP) for more information about troubleshooting sort orders in a project.

I cannot apply a sort order

ShowThe sort buttons are disabled

The sort buttons on the toolbar appears dimmed when the selected column or field does not support sorting. OLE object fields and calculated columns do not support sorting. On a data access page, memo and hyperlink fields do not support sorting.

Tip    Although you cannot sort on calculated columns by using the sort buttons, you can sort based on an expression by using the Advanced Filter/Sort dialog box. See the section "Sort records based on calculated fields or columns" in the topic Sort records.

ShowThe field or column on which I want to sort is not visible

  • When sorting by using the sort buttons, you cannot sort on a field if the corresponding column or control is not visible.
  • In Datasheet view, on the Format menu, click Unhide Columns. In the Unhide Columns dialog box, select the names of the columns that you want to show.
  • If you don't see the field in Form or Page view, open the form or page in Design view and add the field from the field list, and then switch to Form or Page view.

 Note   When sorting by using the Advanced Filter/Sort dialog box, the Sorting and Grouping dialog box, or the OrderBy and OrderByOn properties, the column or control need not be visible.

ShowThe OrderbyOn property is missing in the property sheet

Only reports have this property. A form has only the OrderBy property. When working with a form, if a sort order is specified in the OrderBy property, it will be automatically applied.

ShowCannot find the DefaultSort property in the property sheet

The DefaultSort property is the property of a group level on a data access page. In page Design view, right-click the header or footer bar of a group level, and then click Group Level Properties to view or set the DefaultSort property.

ShowI can't find the language I want in the New database sort order box.

Some languages that were once listed separately are now included in the General option in the list. This option includes the following 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.

I applied a sort order, but...

ShowSome records are not getting sorted properly

If some records are out of place, one or more of the following may be the cause:

  • Leading space    Make sure the values don't begin with a space. When sorted in ascending order, the space character gets precedence over other characters. Remove the leading spaces, and then try sorting again.
  • Special characters    Access does not ignore special characters that precede alphanumeric characters. This might cause values starting with special characters, such as quotes, single quotes, and the minus sign, to appear out of place. To avoid this, try sorting on partial values. If the number of special characters preceding the value remains the same, sort on partial values. For step-by-step information about sorting on partial values, see the section "Sort records based on partial field values" in the topic Sort records. If the number of characters to ignore varies, you can specify a custom sort order. For step-by-step information, see the topic Sort records in custom sort order.
  • New records    When you add records in Datasheet, Form, or Page view, the new records might appear at the end until you sort again.
  • Multi-user environment    In a multi-user environment, another user might have added or changed records. Reapply the sort order often to refresh the view.
  • Expressions    Some expressions might make two different values the same or equal, for sorting purposes. For example, if an expression ignores the minus sign preceding negative values, -3 and 3 might appear together, in the order they were originally entered.
  • Case    Access does not distinguish between uppercase and lowercase strings, so you might see strings, such as "Smith" and "smith," in the order in which they were originally entered. To avoid this, you can either correct the individual values, or apply a case-sensitive sort order by using Visual Basic for Applications (VBA) programming. For step-by-step information, see the article How to sort records in case-sensitive ( ASCII) order.
  • Null and blank values    If the sort field contains null and zero-length strings, records with null values will be listed first when sorted in ascending order, then records with zero-length strings, and then the records with non-blank values.
  • Numbers    If records containing numbers are not sorting properly, check the data type of the column of field. If numbers are stored in a text field, they will be sorted based on their first character instead of on the entire value. For more information about how to avoid this, see the section "Sort numbers stored in a non-numeric field" in the topic Sort records.

ShowRecords are not displayed in the right order

If only some records are out of place, see section "I applied a sort order, but..." in this topic. If the order of all or most records isn't what you expected, see the topic Records are displayed in the wrong order, which lists a number of factors that might be the cause.

ShowSorting seems to slow down the database

If you notice that sorting records is slowing down your database, consider creating indexes based on the sort fields.

You can create indexes based on a single field or on multiple fields. Multiple-field indexes enable you to distinguish between records in which the first field may contain the same value for different records.

ShowCreate a single-field index

  1. Open a table in Design view.
  2. In the upper portion of the window, click the field for which you want to create an index.
  3. In the lower portion of the window, click inside the Indexed property box and then click Yes (Duplicates OK) or Yes (No Duplicates).

ShowCreate a multiple-field index

  1. Open the table in Design view.
  2. Click Indexes Button image on the toolbar.
  3. In the first blank row in the Index Name column, type a name for the index. You can base the index name on one of the existing index fields, or use another name.
  4. In the Field Name column, click the arrow and select the first field for the index.
  5. In the next row of the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index.

 Note   The default sort order is Ascending. Select Descending in the Sort Order column of the Indexes window to sort the corresponding field's data in descending order.

ShowI get the message "The DefaultSort property is invalid."

The string you entered in the property box in page Design view is not valid. Review the DefaultSort property in the group level property sheet. For more information about how to specify a valid string, see the section "Specify a default sort order for a page" in the topic Sort records.

ShowNumbers are sorted based on the first character instead of on their numeric values

This happens when the underlying field is either a text or memo type field. When you sort records based on text values, numbers are sorted alphabetically instead of numerically. For more information about how to avoid this, see the section "Sort numbers stored in a non-numeric field" in the topic Sort records.

ShowI created a report based on a query, but the query sort order is not reflected in the report

If a query was sorted in default sort order at the time the report was created, the report inherits the sort order and displays the records in sorted order. However, the OrderBy and OrderByOn properties of the report are not set. The OrderBy property is left blank, and the OrderByOn property is set to No. This also applies to forms. The form or report must be based on a table, or on a query that is sorted in an order other than the default order, for these properties to be set.

If you see that the records in the report are not sorted, you should find out if another user has removed or replaced your sort order.

ShowSort order is not being saved

Sort order is not saved in the following cases:

  • Page view     Sort orders applied in Page view are not saved
  • Forms    A distinction is not made between default and saved sort orders. When you apply a sort order by using the sort buttons, the OrderBy property is overwritten with the new sort order details.
  • Inherited sort orders    Forms and reports inherit sort orders and display records in sorted order, but if the underlying query was sorted in default order at the time the form or report was created, the sort order is not saved as the form or report's default sort order. In this case, you will find that the OrderBy property is blank and the OrderByOn property is set to No.

ShowSort order is not being applied

A saved sort order is always automatically applied when you open an object. If the object was not saved with a sort order but has a default sort order specified, the records will be displayed in default sort order. If a form or report do not have a saved or default sort order specified, the records will be displayed in the order determined by the inherited sort order. For more information about saved, default, and inherited sort orders, see the section "Save a sort order" in the topic Sort records.

When working with a report, review the OrderByOn property. If the property is set to No, the default sort order will not be applied when the report is opened. To apply the default sort order, set the OrderByOn property to Yes.

In a multi-user environment, it is possible another user may have changed or removed an object's sort order. Review the sort order settings to make sure it is specified. For more step-by-step information about reviewing sort order settings, see the section "Review the sort order settings" in the topic Sort records.

ShowI don't want Access to apply the sort order automatically

If you don't want an object to be sorted in a specific order when you open it next time, remember to remove the current and default sort orders before you save and close the object.

For more step-by-step information about removing sort order settings, see the section "Undo or remove a sort order" in the topic Sort records.

ShowThe sort order is lost during an export operation

When you export the data in an Access database, the sort order is preserved only when you Save Formatted check box in the Export dialog box. If the check box is disabled, or if you don't select it, the sort order will be ignored.

ShowAccess isn't using the sort order I specified in a linked table

ShowI receive replication errors after I change the language sort order

If you're synchronizing between replicas in an Access database, you might encounter synchronization conflicts if the databases don't have the same language sort orders.

Still looking for help?

  • Search the Microsoft Product Support Center    The Support Center has a wealth of troubleshooting information. Search the Access 2003 Support Center for help about issues specific to Access.
  • Ask the Access experts and other users    If a specific error or issue is not covered in this topic, post it to the Access experts and get help specific to your situation. For more information about how to get help from the Access community, see the article Ask the Access community.
 
 
Applies to:
Access 2003