IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, January 2004. Updated February 2007.
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|
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...