Field types in Microsoft Access

Fields in a Microsoft Access database can be of many different data types. Access supports different kinds of text, numbers, dates and some more special data types. This document will describe each data type and note which versions of Microsoft Access support it.

Textual types

Text

The Text data type is used for short text fields of limited length, and can contain up to 255 characters.

Memo

A Memo field is a virtually unlimited text field. It can store up to 1GB of text.

Microsoft Access supports text in a variety of languages and alphabets. Access 97 supported multiple code pages in the same document. This worked great in most cases, but caused problems when columns contained text in different alphabets. Therefore Microsoft switched to the Unicode character encoding in Access 2000. MDB Viewer automatically supports all encodings used in Access.

Numeric Types

Byte, Integer and Long Integer

Integers in Access come in 1, 2 and 4 byte varieties. The single byte number is named Byte (Range 0-255), the two-byte number is named Integer (-32768 to 32767) and then there is the Long Integer (-2 billion to 2 billion).

Decimal

Decimal numbers are so called fixed point numbers. They can have a fractional part, and they can be either positive or negative. Decimals have a fixed number of digits to left and to the right of the decimal point. The maximum total number of digits supported by Access is 28. Decimals are used for exact numeric values. Despite the name "Decimal" you can also use the decimal data type for integers larger than the range supported by the Long Integer type. Decimals are only supported by Microsoft Access 2000 and later. A decimal number uses 17 bytes of disk space.

Currency

The currency type is a special kind of decimal, with up to 4 digits on the right of the decimal point and up to 15 on the left. It was introduced for financial data and is available in all versions of Access. This type uses 8 bytes of disk space.

Float and Double

Floating point numbers are similar to numbers written in scientific notation, and come in two varieties: float and double. They have a precision of up about 7 decimal digits (float) or 15 digits (double). The exponent (location of decimal point) is stored separately, an can range from -45 to +38 (float) or from -324 to 308 (double). Use floating point numbers if you require a large range but only limited precision. Floats use 4 bytes, and doubles use 8 bytes of disk space.

Other Types

Date/Time

Specifies a specific point in time (Date and time of day). Dates are internally stored as an 8 byte double precision floating point numbers, so the range is virtually unlimited. (Dates up to 2 million AD can be stored with a precision of 1 second.) MDB Viewer exports dates in the format YYYY-MM-DD HH:MM:SS.

Yes/No

A boolean value that can be either on or off, displayed as a check box in Microsoft Access. Displayed as 0 or 1 in MDB Viewer.

OLE Object

An OLE object field can contain documents, such as images, text files, Microsoft Word files or Microsoft Excel files. The size of this field is limited to 1 GB. MDB Viewer for Mac supports many kinds of OLE objects, such as Images, Word documents and PDF files. Sometimes Access stores OLE Object in an unknown data structure that I do not understand yet. These files show up as unknown files.
You can extract OLE files by dragging the icon to the Finder.

Complex Types

Microsoft Access 2007 introduced a number of types they call Complex Columns. These types are multivalued fields, attachments, and version history for memo fields. Common to all these fields is that they can contain multiple values per row, seemingly against the rules of database normalisation. However, Access internally stores these values in normalized tables that are hidden from the user. MDB Viewer makes these hidden tables accessible for easy exporting.

Multi-Valued Field

A multi-valued field can contain multiple numbers of any of the types listed above. The actual value stored in the complex field is a key that links to the numbers stored in the helper table.

Attachments

Attachment fields are conceptually similar to multi-valued fields, except that the helper table contains files rather than numbers.

Memo version history

For these fields, the helper table contains the previous contents of a field together with a time stamp.

For an example of working with multi-valued fields, see the section on viewing Access databases.