Incorrect sorting with Decimal fields (bug)

        1 votes: *****     2,60 views      No comments
by Allen Browne, 20 April 2005    (for Access 2000+)

Flaws in Microsoft Access

Provided by, 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, 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.


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.

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Incorrect sorting with Decimal fields (bug)'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).