Data types for Access desktop databases

When creating tables in Access, you need to select a data type for each column of data. The Short Text data type is a popular choice since it lets you enter almost any character (letter, symbol, or number). However, careful selection of data types can help you take advantage of more Access features (such as data validation and functions), and improves the accuracy of the information you’re storing. The table below gives an overview 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, see Introduction to data types and field properties.

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

Data Type

Usage

Size

Short Text (formerly known as “Text”)

Alphanumeric data (names, titles, etc.)

Up to 255 characters.

Long Text (formerly known as “Memo”

Large 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.

Number

Numeric data.

1, 2, 4, 8, or 16 bytes.

Date/Time

Dates and times.

8 bytes.

Currency

Monetary data, stored with 4 decimal places of precision.

8 bytes.

AutoNumber

Unique value generated by Access for each new record.

4 bytes (16 bytes for ReplicationID).

Yes/No

Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.

1 byte.

OLE Object

Pictures, graphs, or other ActiveX objects from another Windows-based application.

Up to about 2 GB.

Hyperlink

A 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).

Attachment

You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn't available in MDB file formats.

Up to about 2 GB.

Calculated

You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn't available in MDB file formats.

Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types.

Lookup Wizard

The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.

Dependent on the data type of the lookup field.

The following table lists the data types available in desktop databases in Access 2010.

Data Type

Usage

Size

Text

Alphanumeric data (names, titles, etc.)

Up to 255 characters.

Memo

Large amounts of alphanumeric data: sentences and paragraphs.

Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.

Number

Numeric data.

1, 2, 4, 8, or 16 bytes.

Date/Time

Dates and times.

8 bytes.

Currency

Monetary data, stored with 4 decimal places of precision.

8 bytes.

AutoNumber

Unique value generated by Access for each new record.

4 bytes (16 bytes for ReplicationID).

Yes/No

Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.

1 byte.

OLE Object

Pictures, graphs, or other ActiveX objects from another Windows-based application.

Up to about 2 GB.

Hyperlink

A 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).

Attachment

You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn't available in MDB file formats.

Up to about 2 GB.

Calculated

You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn't available in MDB file formats.

Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types.

Lookup Wizard

The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.

Dependent on the data type of the lookup field.

The following table lists the data types available in desktop databases in Access 2007.

Data Type

Usage

Size

Text

Alphanumeric data (names, titles, etc.)

Up to 255 characters.

Memo

Large amounts of alphanumeric data: sentences and paragraphs.

Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.

Number

Numeric data.

1, 2, 4, 8, or 16 bytes.

Date/Time

Dates and times.

8 bytes.

Currency

Monetary data, stored with 4 decimal places of precision.

8 bytes.

AutoNumber

Unique value generated by Access for each new record.

4 bytes (16 bytes for ReplicationID).

Yes/No

Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.

1 byte.

OLE Object

Pictures, graphs, or other ActiveX objects from another Windows-based application.

Up to about 2 GB.

Hyperlink

A 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).

Attachment

You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn't available in MDB file formats.

Up to about 2 GB.

Lookup Wizard

The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.

Dependent on the data type of the lookup field.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×