Problems with Decimal Fields in Microsoft Access Tables

Provided by: Molly Pell, Program Analyst

In Microsoft 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!

Return to the tips page