Problems with Decimal Fields in Microsoft Access Tables
Provided by: Molly Pell,
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
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
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
This field size is new in Access 2000, and exporting to Access 97 or
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.
This tip and more provided by Total
Return to the tips page