You are currently offline, waiting for your internet to reconnect

BUG: You may notice an incorrect sorted order when you sort the negative decimal values in descending order in Access

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.



This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
When you sort the records in a Number field in descending order, and the Field Size property of the Number field is set to Decimal, you may notice that the negative values in the field are not sorted in the correct order. This problem occurs in a Microsoft Access table.

For example, you can sort a Number field in descending order that contains the following data:

20, -10, 5, -5, 10, -20

After you sort the Number field in descending order, you may notice that the sorted data appears in the following order:

-5,-10,-20,20,10,5
WORKAROUND
To work around this problem, create an index on the Number field before you sort the records in the Number field. To do this, follow these steps:
  1. Start Access.
  2. Open your Access database.
  3. In the Database window, click Tables under the Objects section.
  4. In the right pane, right-click the table that contains the problem field, and then click Design View.
  5. Select the problem field.
  6. In the Field Properties section, set the value of the Indexed property to Yes (Duplicate OK).
  7. On the File menu, click Save.
  8. On the View menu, click Datasheet View.
  9. Select the problem field.
  10. On the Records menu, point to Sort, and then click Sort Descending.

    Notice that the sorted data appears in the correct order.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION

Steps to reproduce the problem

  1. Start Access.
  2. Open the Northwind.mdb sample database.
  3. In the Database window, click Tables under the Objects section.
  4. In the right pane, double-click Create table in Design view.
  5. Create a table that is named TestTbl that has two fields that are named fldID and fldNumber. Set the properties of the fields as follows:

    Table: TestTbl   ----------------------------   Field Name	: fldID   Data Type	: AutoNumber   Field Name	: fldNumber   Data Type	: Number   Field Size	: Decimal   Table Properties: TestTbl   -------------------------------   PrimaryKey: fldID
  6. On the File menu, click Save.
  7. In the Save As dialog box, type TestTbl in the Table Name box, and then click OK.
  8. On the View menu, click Datasheet View.
  9. Enter the following data in the fldNumber field:

    20, -10, 5, -5, 10, -20
  10. Select the fldNumber field.
  11. On the Records menu, point to Sort, and then click Sort Descending.

    Notice that the sorted data appears in the incorrect order.
REFERENCES
For more information about indexes in Access 2003, click Microsoft Office Access Help on the Help menu, type About indexing fields and records in an Access database (.mdb) in the Search for box in the Assistance pane, and then click Start searching to view the topic.

For more information about indexes in Access 2002 and Access 2000, click Microsoft Access Help on the Help menu, type About indexing fields and records in an Access database in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about sorting in Access 2003, click Microsoft Office Access Help on the Help menu, type About sorting records in the Search for box in the Assistance pane, and then click Start searching to view the topic.

For more information about sorting in Access 2002 and Access 2000, click Microsoft Access Help on the Help menu, type About sorting records in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Acc2000 Acc2002 Acc2003 wrong sorted order
Properties

Article ID: 837148 - Last Review: 07/28/2006 03:05:30 - Revision: 1.2

Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition, Microsoft Access 2000 Standard Edition

  • kbprogramming kbjet kbfield kbproperties kbtable kbbug KB837148
Feedback
m/c.gif?DI=4050&did=1&t=">=">