Advertisements

Data types for Access desktop databases

Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

While creating tables in Access, you must choose a data type for each column of data. The Short Text data type is a common choice since it allows you to input virtually every character (letter, symbol, or number). However, tactical selection of data types can help you optimise further Access features (such as data validation and functions), and raises the accuracy of the information you’re storing. The table beneath provides a summary of the data types available in Access desktop databases (.accdb and .mdb). For information about data types in Access web apps, see Data types for Access web apps.

For a comprehensive list of all the various field properties available for the various data types, read Introduction to data types and field properties.

Newer versions

The following table lists the data types available in desktop databases in Access 2013 and later versions.

Data TypeUsageSize
Short Text (formerly known as “Text”)Alphanumeric data (names, titles, etc.)Up to 255 characters.
Long Text (formerly known as “Memo”Vast amounts of alphanumeric data: sentences and paragraphs. See The Memo data type is now called “Long Text” for more information on the Long Text details.Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.
NumberNumeric data.1, 2, 4, 8, or 16 bytes.
Large NumberNumeric data.8 bytes.

For more information, see Using the Large Number data type.
Date/TimeDates and times.8 bytes.
CurrencyMonetary data, stored with 4 decimal places of precision.8 bytes.
AutoNumberUnique value created by Access for every new record.4 bytes (16 bytes for ReplicationID).
Yes/NoBoolean (true/false) data; Access holds the numeric value zero (0) for false, and -1 for true.1 byte.
OLE ObjectPictures, graphs, or other ActiveX objects from another Windows-based application.Up to about 2 GB.
HyperlinkA link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer.Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).
AttachmentYou can attach files like pictures, documents, spreadsheets, or charts; every Attachment field can include an unlimited number of attachments per record, reaching the storage capacity of the size of a database file. Note, the Attachment data type is unavailable in MDB file formats.Up to about 2 GB.
CalculatedYou can formulate an expression that applies data from one or more fields. You can construct different result data types from the expression. Note, the Calculated data type is unavailable in MDB file formats.Influenced by the data type of the Result Type property. Short Text data type result can contain up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their corresponding data types.
Lookup WizardThe Lookup Wizard entry in the Data Type column in Design view is not truly a data type. After you pick this entry, a wizard begins to assist you with defining a simple or complex lookup field.

A simple lookup field employs the contents of another table or a value list to validate the contents of a single value per row.

A complex lookup field enables you to preserve several values of the equivalent data type in individual rows.
Based upon the data type of the lookup field.
Advertisements

Leave a Reply

%d bloggers like this: