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.

Database programs, including Microsoft Access, require you to choose a data type whenever you create a field in a table. This data type, along with a field size and input mask (where appropriate), are an attempt by the database program to block improper data from entering into the database.

One example would be the creation of a date field. This data type would not allow you to enter the word ‘month’ – you can only enter a date. Likewise, a number field created to store inventory counts would not allow the word ‘twenty’ only the numeral ‘20’.

Novice Access users are often confused about the very similar text and memo fields.

A text data type can hold a maximum of 255 alpha-numeric characters.

A memo data type can hold a maximum of 65,536 alpha-numeric characters if entered through the user interface (i.e. a form). It can store a maximum of one gigabyte’s worth of characters if the data is being entered programmatically.

The question is why would you choose the limited text field when you can assign a memo data type and store your great American classic in one field of one record? The answer, although simple, is important when you want to query or sort your data.

You cannot search, filter, or query a memo field. [Note: a memo field can be sorted, but Access only uses the first 255 characters of data.]

The memo data type is designed to hold data that although important, is not meant to be searched. The classical example of a memo field is to record comments, such as supervisor comments on an employee evaluation. You do not search the comments, you search for the employee (or supervisor) and then review the comments stored for that employee.

The text field should be used to store data that (a) will be used for searching, filtering, or querying and (b) will not be used for calculations.