Microsoft Access: Number vs. Currency Fields

In a prior post we explained the difference between the text and memo data types. This post will cover the topic of why you should choose a currency data type rather than the number data type.

The currency data type is used to only store numerals – no text characters. It is accurate to fifteen digits to the left of the decimal point and four digits to the right of the decimal point. (Since fifteen digits equates to trillions you could use Access to track the federal debt.)

The number data types are also used to store numerals – no text characters. The accuracy of the number data type is based on the chosen field size.

A field size of byte, integer, or long integer allow whole numbers only – no decimal points. A byte allows positive values from 0-255. An integer can accept positive or negative values from -32768 to 32767 while the long integer field size accepts values from -2147483648 to 2147483647 (approximately 2.1 billion). If you need to store numbers with decimals you have to choose the decimal, single, or double field size. These field sizes accept both positive and negative numbers, and the size of the numbers is expressed in either powers of ten (10^38-1) or scientific notation (3.402823E38).

Due to the internal mechanics of how computers store large numbers, a computer may consider 1.00000000000001 to be equal to 1.00000000000002 although we can see that these two numbers are not exactly equal. Although the difference is immaterial you can never be 100% sure how Access would use or compare these numbers.

The currency data type was created to avoid the small differences that appear to the far right of the decimal point. This data type should be used to store financial data, or data that will be used in calculations, requiring a high degree of accuracy.