Provided by Allen Browne, January 2004. Updated January 2010.
Jan 2010 Update: Duane Hookom (Access MVP) has demonstrated another bug with Decimal fields. Access 2000 - 2003 truncates the fractional part in a text box bound to =Sum([Field1]) where Field1 comes from a Totals query that sums a Decimal field. A2007 does not have the bug.
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.
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 |