Incorrect sorting with Decimal fields (bug)

        1 vote: ***       2,063 views      0 comments
by Allen Browne, 20 April 2005    (Access 2000+)

Flaws in Microsoft Access

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


Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your 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...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.