Flaws in Microsoft Access

Provided by allenbrowne.com, January 2004. Updated February 2007.

Incorrect sorting with Decimal fields

Feb 2007 Update: This bug is partially fixed in Access 2007. Applies to Access 2000 - 2003.

It is hard to believe that Access could get this simple query wrong:

    SELECT MyField FROM MyTable ORDER BY MyField DESC;

If the MyField is a Decimal type and you ask for descending order, the sorting is wildly inaccurate.

To demonstrate the flaw with AccessFlaws.zip, open the query named BadSortQuery. Negative values appear first (wrong), followed by the positive ones. Nulls and zeros sort unpredictably - at the beginning, middle or end, depending on the data.

Workaround

This bug has now been documented in Microsoft's knowledgebase article 837148, with the suggestion to index the field to help Access sort it. That will not get you out of trouble if you ever need a query to perform aggregation on the field (such as summing).

If you must work with Decimal fields, you could use CDbl() or CCur() to typecast the expression in the ORDER BY clause. Naturally this is very inefficient, requiring conversion at every row.

The only real solution is to avoid using the Decimal field type. It is incompletely implemented anyway. VBA has no Decimal data type. Decimal as a subtype of Variant is vague and inefficient, and there is no way to declare a constant of type Decimal. And there are other bugs with the Decimal field type, such as export failures documented in k.b. 263946.