Access: Numbers

As we touched on last month, if the data type for a field is set to number, the field size property provides the greater details or specification about what type of number that field will contain. Figure 1 shows some of the options available for numbers-based data and the ranges of values each option covers.

WHOLE NUMBERS

There are various types of field sizes for whole numbers. A byte is a whole number ranging from 0 to 255. While some Access property settings are stored as byte, it isn’t always a good choice to use since the definition of byte is different in other systems, like SQL Server.

Integers are whole numbers between -32,768 and 32,767. Integer is good to use instead of byte in most cases because integer is more common and transportable to other systems. If a value such as a month number that won’t go above 12 is stored, it’s often better to use integer. Many of the date/time functions use integer. It’s also sometimes better to use integer rather than Yes/No (which is stored internally as a number) because an integer field can be null (have no value) and can be displayed using a checkbox.

Long integers cover whole numbers ranging from approximately -2 billion to 2 billion. With so many possible values, long integers are excellent for auto-numbering records with unique values. That’s why the field size defaults to long integer whenever you specify AutoNumber as the data type for the field. In related tables, a foreign key field that links back to the AutoNumber primary key would be set to have a data type of number and field size of long integer.

When defining a field to be a number, note that most versions of Access automatically set the default value to 0. Many times, especially with a foreign key, a default value isn’t wanted, so remember to delete anything that may be specified.

FLOATING POINT NUMBERS

“Floating point” means that the decimal point can “float” and can be placed anywhere relative to the significant digits of the number. Floating point numbers are stored in scientific notation and used when the decimal form isn’t practical to express numbers that are very big or very small.

Within the number data type, there are two field sizes for floating point numbers: single and double precision numbers. A single precision number has between 6 and 9 significant figures, with 7 as the most common. (For example, if 1,234,567,890 is entered into the field, Access will display 1.234568E+09.) A double precision number can contain 15-17 significant figures.

Because floating point numbers aren’t precise, they should never be used as primary or foreign keys to link tables. Neither should they be compared using an equal sign. While not exact, they are consistent and predictable. If you enter 1.4E-45 in a single precision field, 1.401298E-45 will be displayed since that’s the closest representation with how floating point binary is done. Alternately, 1.4E-30 will display as entered.

When creating tables by importing from other databases, be aware that some of the number fields will default to a field size of double and will need to be changed to long integer, text, or something else.

CURRENCY

Currency is a specific data type, separate from the field sizes for number fields that we’ve been talking about thus far. Values in currency fields can range between approximately -922 trillion to 922 trillion. The field can include up to 15 digits before the decimal point and is accurate to four decimal places.

Currency can be used even when the data for the field isn’t monetary, such as when the quantities include fractions. Within Access, currency also is the most accurate numeric data type with decimal places. The format property can be set so a currency symbol (like $) isn’t displayed.

TEXT

Sometimes data contains numbers but shouldn’t be stored that way because it won’t be used in mathematical calculations. In these cases, short text (or text in older Access versions) can be used. Set the field size to the maximum number of characters there might be. Examples include phone numbers, ZIP codes, account codes, and imported record numbers if values are too big for long integer or have necessary leading zeroes.

DISPLAYING NUMBERS

Just as in Excel, Access will display octothorps (######) if there isn’t enough room to display the entire value. There are a number of ways to resolve this: Increase the width of the column or control, decrease the text font size, reduce margins and padding, modify the format of the field or control, and so forth.

The default display of numbers and dates is controlled by the Windows Region Settings, which vary depending on your location (choose “Region” from the Control Panel). Figure 2 shows an example of the Customize Format dialog box in Windows for Numbers.

Download this month’s database: sf-1703-AccessNumbers. 

SF SAYS: