Access Numerical Data Types
If you want to store numerical values in your Access database you have seven possible choices.
The two key questions you want to ask yourself are:
- What are the largest and smallest values the field will hold?
- Will the field require decimal places?
Remember that changing a data type after the fact is usually a big problem so getting the table design correct is very important. When in doubt choose a larger data type than a smaller one.
Six Access Database Numerical Data Types
Within the Numerical data type there are six possible choices. Each choice differs based on
- The largest and smallest values it can store,
- Whether or not it can hold decimal places and
- How it treats decimal values
Over the next few pages I will do a deeper dive into each data type and what it can hold.
Access Database Byte Data Type
The Byte data type stores whole numbers from 0 to 255and takes up the least amount of space of the numerical data types.
A Byte data type is useful to hold locations or statuses with a small number of possible positive values.
For example, if you have five different office locations you might want assign each office a value and store this as a Byte field – location #1 is the home office, location #2 is the Seattle office and location #3 is the San Francisco office.
Access Database Integer Data Type
A Byte data type is good for small positive values but what if you need to store larger numbers or negative numbers?
Welcome to two new data types: Integer and Long Integer.
An Integer data type stores whole numbers +/- 32,000 and a Long Integer will store much larger values. A Long Integer data type stores whole numbers +/- 2.15 billion.
The limitation with both the Integer and Long Integer data types is that they only store whole numbers – they do not store decimal values
Access Database Double Datatype
If you need to store numbers with decimal places you can use a Single or Double data type.
The Single and Double data types are short hand for “single precision floating-point variables” or “double precision floating-point variables”, respectively.
Floating point variables store numbers using scientific notation. Floating point math gets technical but the bottom line is that these data types can store both very large numbers and very small fractional numbers
Access Database Decimal Data Type
Another data type that will store fractions is the Decimal data type
The Decimal data type will store up to 28 decimal places. The Decimal data type is more useful for scientific or medical research calculations that require very small numbers.
For example, the Decimal data type is useful if you’re a scientist storing measurements in micrometers or analyzing sizes of atoms because it will hold many, many decimal places.
For financial calculations the Decimal data type is not used because the Currency data type is better designed to hold financial information.
Access Database Currency Data Type
The currency data type has its own whole category in Access, separate from other numerical data types. I think the Currency data type in Access is broken into its own category to allow for fancy formatting of dollar or currency amounts.
The Currency data type stores values up to 1028 with four decimal places of precision.
The important thing is that storing values with four decimal places of precision prevents subtle rounding errors that can occur with single and double precision floating-point calculations.
Where To Go Next
Access Text Data Type Tutorial
Access Numerical Data Types Tutorial
Access Date Data Type Tutorial
No comments yet.