|
Free Resources from FMS |
|
|
|
|
"Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities." - Darren D. |
|
|
|
|
Microsoft
Access Resource Center
Problems with Decimal Fields in Access Tables
Provided by: Molly Pell, Program Analyst
In Access tables, Number fields have various Field Size options that determine the type of numbers that the field accepts. Among the available Field Sizes is Decimal:
Decimal fields have decimal precision of 28, and store numbers from –10^28–1 through 10^28–1 (or –10^38–1 through 10^38–1 in ADPs).
Note, however, that Decimal fields have several known issues that limit their usefulness, and they should be avoided whenever possible.
The main issue is with sorting decimal fields in descending order. Zeros and negative values are the sorted incorrectly, as demonstrated in the screenshot below:
In the above example, negative values
appear before positive values, and 0 appears at the end of the list.
This issue is documented in Microsoft’s KB article 837148.
In addition to the sorting problem, there are several other problems
with Decimal types, including:
-
Decimal types use more storage space than other numeric field types.
-
Exporting tables with Decimal fields to a text file fails (documented in KB article 263946)
-
This field size is new in Access 2000, and exporting to Access 97 or earlier fails.
-
There is no Decimal data type in VBA—there’s no way to declare a constant with this type.
The best solution is to avoid using Decimal field size altogether—instead, use the Double field size for numeric fields that require decimal values.
References:
http://support.microsoft.com/?id=837148
http://support.microsoft.com/?id=263946
http://allenbrowne.com/bug-08.html
This tip and more provided by Total Access Analyzer!
