Introduction to data types and field properties

This article provides an overview of data types and field properties, and includes a detailed data type reference section. This article also briefly explains Lookup fields (Lookup field: A field, used on a form or report in an Access database, that either displays a list of values retrieved from a table or query, or stores a static set of values.). This article does not discuss Lookup fields that allow multiple values. Find links to more information about Lookup fields that allow multiple values in the See Also section.

In this article


Overview

Every field (field: An element of a table that contains a specific item of information, such as a last name. A Title field might contain Mr. or Ms. Databases such as Microsoft SQL Server refer to fields as columns.) in a table has properties. These properties define the field's characteristics and behavior. The most important property for a field is its data type. A field's data type determines what kind of data it can store. For example, a field whose data type is Text can store data that consists of either text or numerical characters, but a field whose data type is Number can store only numerical data.

A field's data type determines many other important field qualities, such as:

  • How you can use the field in expressions.
  • The maximum size of a field value.
  • Whether the field can be indexed.
  • Which formats can be used with the field.

When you create a new field in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), you specify the field's data type and, optionally, its other properties.

Table Design view

Contacts table open in Design view
Callout 1 Data type
Callout 2 Field properties

When you create a field in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.), the field's data type is defined for you. If you create a field in Datasheet view by using a field template or by using an existing field from another table, the data type is already defined in the template or in the other table. If you create a field by entering data in Datasheet view, Microsoft Office Access assigns a data type to the field based on the values that you enter. If you enter values that are of different data types in the field, Access may prompt you to make a decision about the data type.

You can change the field's data type and its Format, Indexed, and Required field properties in Datasheet view.

Table Datasheet view

Contacts table open in Datasheet view
Callout 1 Create a field by entering data in an empty column.
Callout 2 Adjust the field's data type and other properties by using the Datasheet tab on the Ribbon.

Data types

Think of a field's data type as a set of qualities that applies to all the values contained in the field and that determines what kind of data those values can be. For example, values that are stored in a Text field can contain only letters, numbers, and a limited set of punctuation characters. In addition, a Text field can contain a maximum of 255 characters.

There are ten different data types in Access:

 Tip    Try Office 2010 Have you ever wanted add a calculated field to a table? In Access 2010, you can. Read an article or try Office 2010!

 Tip   Sometimes, the data in a field may appear to be one data type, but is actually another. For example, a field may seem to contain numeric values but may actually contain text values, such as room numbers. You can often use an expression to compare or convert values of different data types.

Lookup fields

You can set a field's data type to Lookup Wizard. Doing this starts the Lookup Wizard, which helps you create a Lookup field. A Lookup field displays either a list of values that is retrieved from a table or query, or it displays a static set of values that you specified when you created the field.

In the Lookup Wizard, you can enter either a static list of values or specify a source for the values that you want to retrieve, such as a field in a table. The data type of a Lookup field is either Text or Number, depending on the choices that you make in the wizard.

 Note   Lookup fields have an additional set of field properties, which are located on the Lookup tab in the Field Properties pane.

Find links to more information about Lookup fields in the See Also section.

Field properties

After you create a field and set its data type, you can set additional field properties. The field's data type determines which other properties you can set. For example, you can control the size of a Text field by setting its Field Size property.

For Number and Currency fields, the Field Size property is particularly important, because it determines the range of field values. For example, a one-bit Number field can store only integers ranging from 0 to 255.

The Field Size property also determines how much disk space each Number field value requires. Depending on the field size, the number can take up exactly 1, 2, 4, 8, 12, or 16 bytes.

 Note   Text and Memo fields have variable field value sizes. For these data types, Field Size sets the maximum space available for any one value.

You can find more information about field properties and how they work with the different data types in the data type reference section of this article.

Data types in relationships and joins

A table relationship is an association that is established between common fields (columns) in two tables. A relationship can be one-to-one (one-to-one relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of one, and only one, record in the related table.), one-to-many (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.), or many-to-many (many-to-many relationship: An association between two tables in which one record in either table can relate to many records in the other table. To establish one, create a third table and add the primary key fields from the other two tables to this table.).

A join is an SQL operation that combines data from two sources into one record in a query recordset (recordset: The collective name given to table-, dynaset-, and snapshot-type Recordset objects, which are sets of records that behave as objects.) on the basis of values in a specified field that the sources have in common. A join can be an inner join (inner join: A join where records in two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.), a left outer join (left outer join: An outer join in which all the records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the right.), or a right outer join (right outer join: An outer join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left.).

When you create a table relationship or add a join to a query, the fields that you connect must have the same or compatible data types. For example, you cannot create a join between a Number field and a Text field, even if the values in those fields match.

 Note   In a relationship or a join, fields that are set to the AutoNumber data type are compatible with fields that are set to the Number data type if the Field Size property of the latter is Long Integer.

 Tip   In a query, you can sometimes use the Like operator in a field criterion to compare fields that have incompatible data types.

You cannot change the data type or the Field Size property of a field that is involved in a table relationship. You can temporarily delete the relationship to change the Field Size property, but if you change the data type, you won't be able to re-create the relationship without first also changing the data type of the related field.

Find more information about table relationships or joins in the See Also section.

Top of Page Top of Page

Data type reference

The following section contains detailed information about data types —their purpose and how they work.



Attachment

Purpose    Use an attachment field to attach multiple files, such as images, to a record.

Suppose that you have a job contacts database. You can use an attachment field to attach a photo of each contact, and you can also attach one or more resumes for a contact to the same field in that record.

For some file types, Access compresses each attachment as you add it.

ShowTypes of attachments that Access compresses

When you attach any of the following file types, Access compresses the file.

  • Bitmaps, such as .bmp files
  • Windows Metafiles, including .emf files
  • Exchangeable File Format files (.exif files)
  • Icons
  • Tagged Image File Format files

You can attach many different types of files to a record, but some file types that may pose security risks are blocked. As a rule, you can attach any file that was created in one of the 2007 Microsoft Office system programs. You can also attach log files (.log), text files (.text, .txt), and compressed .zip files. For a list of supported image file formats, see the table later in this section.

ShowList of blocked file types

Access blocks the following types of attached files.

.ade .ins .mda .scr
.adp .isp .mdb .sct
.app .its .mde .shb
.asp .js .mdt .shs
.bas .jse .mdw .tmp
.bat .ksh .mdz .url
.cer .lnk .msc .vb
.chm .mad .msi .vbe
.cmd .maf .msp .vbs
.com .mag .mst .vsmacros
.cpl .mam .ops .vss
.crt .maq .pcd .vst
.csh .mar .pif .vsw
.exe .mas .prf .ws
.fxp .mat .prg .wsc
.hlp .mau .pst .wsf
.hta .mav .reg .wsh
.inf .maw .scf

Supported field properties

Property Use
Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Required Requires that each record has at least one attachment for the field.

Supported image file formats

Access supports the following graphic file formats without the need for additional software being installed on your computer.

  • Windows Bitmap (.bmp files)
  • Run Length Encoded Bitmap (.rle files)
  • Device Independent Bitmap (.dib files)
  • Graphics Interchange Format (.gif files)
  • Joint Photographic Experts Group (.jpe, .jpeg, and .jpg files)
  • Exchangeable File Format (.exif files)
  • Portable Network Graphics (.png files)
  • Tagged Image File Format (.tif and .tiff files)
  • Icon (.ico and .icon files)
  • Windows Metafile (.wmf files)
  • Enhanced Metafile (.emf files)

File naming conventions

The names of your attached files can contain any Unicode character supported by the NTFS file system that is used in Microsoft Windows NT. In addition, file names must conform to the following guidelines:

  • Names must not exceed 255 characters, including the file name extensions.
  • Names cannot contain the following characters: question marks (?), quotation marks ("), forward or backward slashes (/ \), opening or closing brackets (< >), asterisks (*), vertical bars or pipes (|), colons (:), or paragraph marks (¶).

Top of Page Top of Page

AutoNumber

Purpose    Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key, especially when no suitable natural key (a key that is based on a data field) is available.

An AutoNumber field value requires 4 or 16 bytes, depending on the value of its Field Size property.

Suppose that you have a table that stores contacts' information. You can use contact names as the primary key for that table, but how do you handle two contacts with exactly the same name? Names are unsuitable natural keys, because they are often not unique. If you use an AutoNumber field, each record is guaranteed to have a unique identifier.

 Note   You should not use an AutoNumber field to keep a count of the records in a table. AutoNumber values are not reused, so deleted records can result in gaps in your count. Moreover, an accurate count of records can be easily obtained by using a Totals row in a datasheet.

Supported field properties

Property Use
Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Field Size

Determines the amount of space that is allocated for each value. For AutoNumber fields, only two values are allowed:

  • The Long Integer field size is used for AutoNumber fields that are not used as replication IDs. This is the default value. You should not change this value unless you are creating a replication ID field.

 Note   Replication is not supported in databases that use a new file format, such as .accdb.

This setting makes AutoNumber fields compatible with other Long Integer Number fields when they are used in relationships or joins. Each field value requires 4 bytes of storage.

  • The Replication ID field size is used for AutoNumber fields that are used as replication IDs in a database replica. Do not use this value unless you are working in or implementing the design of a replicated database.

Each field value requires 16 bytes of storage.

Format If you are using an AutoNumber field as a primary key or as a Replication ID, you should not set this property. Otherwise, choose a number format that meets your specific needs.
Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key. Without a unique index, it is possible to enter duplicate values, which can break any relationships in which the key is a part.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. Find more information about creating indexes in the See Also section.

New Values

Determines whether an AutoNumber field increments with each new value or uses random numbers. Select one of the following:

  • Increment   Starts with the value 1 and incrementally increases by 1 for each new record.
  • Random   Starts with a random value and assigns a random value to each new record. Values are of the Long Integer field size, and range from -2,147,483,648 to 2,147,483,647.
Smart Tags Attaches a smart tag (action tags: Data recognized and labeled as a particular type. For example, a person's name or the name of a recent Microsoft Outlook e-mail message recipient is a type of data that can be recognized and labeled with an action tag.) to the field.
Text Align

Specifies the default alignment of text within a control.

Top of Page Top of Page

Currency

Purpose    Use to store monetary data.

Data in a Currency field is not rounded off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. Each Currency field value requires 8 bytes of storage.

Supported field properties

Property Use
Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Decimal Places

Specifies the number of decimal places to use when displaying numbers.

Default Value

Automatically assigns the specified value to this field when a new record is added.

Format Determines the way that the field appears when it is displayed or printed in datasheets or in forms or reports that are bound to the field. You can use any valid number format. In most cases, you should set the Format value to Currency.
Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. For more information about how to create indexes, refer to the See Also section.

Input Mask

Displays editing characters to guide data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.

Required

Requires that data be entered in the field.

Smart Tags Attaches a smart tag to the field.
Text Align

Specifies the default alignment of text within a control.

Validation Rule

Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text

Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page Top of Page

Date/Time

Purpose    Use to store time-based data.

Supported field properties

Property Use
Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Default Value

Automatically assigns the specified value to this field when a new record is added.

Format

Determines the way that the field appears when it is displayed or printed in datasheets, or in forms or reports that are bound to the field. You can use a predefined format or build your own custom format.

ShowList of predefined formats

  • General Date   By default, if the value is a date only, no time is displayed; if the value is a time only, no date is displayed. This setting is a combination of the Short Date and Long Time settings.

Examples    

  • 4/3/07
  • 05:34:00 PM
  • 4/3/07 05:34:00 PM
  • Long Date   Same as the Long Date setting in the regional settings of Windows. Example: Saturday, April 3, 2007.
  • Medium Date   Displays the date as dd-mmm-yyyy. Example: 3-Apr-2007.
  • Short DateSame as the Short Date setting in the regional settings of Windows. Example: 4/3/07.

 Warning   The Short Date setting assumes that dates between 1/1/00 and 12/31/29 are twenty-first century dates (that is, the years are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth century dates (that is, the years are assumed to be 1930 to 1999).

  • Long Time   Same as the setting on the Time tab in the regional settings of Windows. Example: 5:34:23 PM.
  • Medium Time   Displays the time as hours and minutes separated by the time separator character, followed by an AM/PM indicator. Example: 5:34 PM.
  • Short Time   Displays the time as hours and minutes separated by the time separator, using a 24-hour clock. Example: 17:34.

ShowLists of components that you can use in custom formats

Type any combination of the following components to build a custom format. For example, to display the week of the year and day of the week, type ww/w.

 Important   Custom formats that are inconsistent with the date/time settings specified in Windows regional settings are ignored. For more information about Windows regional settings, see Windows Help.

Separator components

 Note   Separators (separator: A character that separates units of text or numbers.) are set in the regional settings of Windows.

:   Time separator. For example, hh:mm


/   Date separator. For example, mmm/yyyy


Any short string of characters, enclosed in quotation marks ("")  Custom separator. Quotation marks are not displayed. For example, "," displays a comma.


Date format components

d   Day of the month in one or two numeric digits, as needed (1 to 31).


dd   Day of the month in two numeric digits (01 to 31).


ddd   First three letters of the weekday (Sun to Sat).


dddd   Full name of the weekday (Sunday to Saturday).


w   Day of the week (1 to 7).


ww   Week of the year (1 to 53).


m   Month of the year in one or two numeric digits, as needed (1 to 12).


mm   Month of the year in two numeric digits (01 to 12).


mmm   First three letters of the month (Jan to Dec).


mmmm   Full name of the month (January to December).


q   The quarter of the year (1 to 4).


y   Number of the day of the year (1 to 366).


yy   Last two digits of the year (01 to 99).


yyyy   Full year (0100 to 9999).


Time format components

h   Hour in one or two digits, as needed (0 to 23).


hh   Hour in two digits (00 to 23).


n   Minute in one or two digits, as needed (0 to 59).


nn   Minute in two digits (00 to 59).


s   Second in one or two digits, as needed (0 to 59).


ss   Second in two digits (00 to 59).


Clock format components

AM/PM   Twelve-hour clock with the uppercase letters "AM" or "PM," as appropriate. For example, 9:34PM.


am/pm   Twelve-hour clock with the lowercase letters "am" or "pm," as appropriate. For example, 9:34pm.


A/P   Twelve-hour clock with the uppercase letter "A" or "P," as appropriate. For example, 9:34P.


a/p   Twelve-hour clock with the lowercase letter "a" or "p," as appropriate. For example, 9:34p.


AMPM   Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows.


Predefined formats

c   Same as the General Date predefined format.


ddddd   Same as the Short Date predefined format.


dddddd   Same as the Long Date predefined format.


ttttt   Same as the Long Time predefined format.



IME Mode

Controls the conversion of characters in East Asian versions of Windows.

IME Sentence Mode

Controls the conversion of sentences in East Asian versions of Windows.

Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. For more information about creating indexes, see the See Also section.

Input Mask

Displays editing characters to guide data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.

Required

Requires that data be entered in the field.

Show Date Picker

Specifies whether to show the Date Picker control.

 Note   If you use an input mask for a Date/Time field, the Date Picker control is unavailable regardless of how you set this property.

Smart Tags Attaches a smart tag to the field.
Text Align

Specifies the default alignment of text within a control.

Validation Rule

Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text

Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page Top of Page

Hyperlink

Purpose    Use to store a hyperlink, such as an e-mail address or a Web site URL.

A hyperlink can be a UNC (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) path or a URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.). It can store up to 2048 characters.

Supported field properties

Property Use
Allow Zero Length

Allows entry (by setting to Yes) of a zero-length string ("") in a Hyperlink, Text, or Memo field.

Append Only

Determines whether to track field value changes. There are two settings:

  • Yes   Tracks changes. To view the field value history, right-click the field, and then click Show column history.
  • No   Does not track changes.

 Warning   Setting this property to No deletes any existing field value history.

Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Default Value

Automatically assigns the specified value to this field when a new record is added.

Format Determines the way that the field appears when it is displayed or printed in datasheets or in forms or reports that are bound to the field. You can define a custom format for a Hyperlink field. Find more information about defining a format in the See Also section.
IME Mode

Controls the conversion of characters in East Asian versions of Windows.

IME Sentence Mode

Controls the conversion of sentences in East Asian versions of Windows.

Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. For more information about creating indexes, see the See Also section.

Required

Requires that data be entered in the field.

Smart Tags Attaches a smart tag to the field.
Text Align

Specifies the default alignment of text within a control.

Unicode Compression

Compresses text that is stored in this field when less than 4,096 characters are stored.

Validation Rule

Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text

Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page Top of Page

Memo

Purpose    Use to store a block of text that is more than 255 characters in length and is formatted text.

Supported field properties

Property Use
Allow Zero Length

Allows entry (by setting to Yes) of a zero-length string ("") in a Hyperlink, Text, or Memo field.

Append Only

Determines whether to track field value changes. There are two settings:

  • Yes   Tracks changes. To view the field value history, right-click the field, and then click Show column history.
  • No   Does not track changes.

 Warning   Setting this property to No deletes any existing field value history.

Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Default Value

Automatically assigns the specified value to this field when a new record is added.

Format Determines the way that the field appears when it is displayed or printed in datasheets or in forms or reports that are bound to the field. You can define a custom format for a Memo field. Find more information about defining a format in the See Also section.
IME Mode

Controls the conversion of characters in East Asian versions of Windows.

IME Sentence Mode

Controls the conversion of sentences in East Asian versions of Windows.

Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. For more information about creating indexes, see the See Also section.

Required

Requires that data be entered in the field.

Smart Tags Attaches a smart tag to the field.
Text Align

Specifies the default alignment of text within a control.

Unicode Compression

Compresses text that is stored in this field when less than 4,096 characters are stored.

Validation Rule

Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text

Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page Top of Page

Number

Purpose    Use to store a numeric value that isn't a monetary value. If you might use the values in the field to perform a calculation, use the Number data type.

Supported field properties

Property Use
Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Decimal Places

Specifies the number of decimal places to use when displaying numbers.

Default Value

Automatically assigns the specified value to this field when a new record is added.

Field Size

Select one of the following:

  • Byte — Use for integers that range from 0 to 255. Storage requirement is 1 byte.
  • Integer — Use for integers that range from -32,768 to 32,767. Storage requirement is 2 bytes.
  • Long Integer — Use for integers that range from -2,147,483,648 to 2,147,483,647. Storage requirement is 4 bytes.

 Tip   Use Long Integer when you create a foreign key to relate to another table's AutoNumber primary key field.

  • Single   Use for numeric floating point values that range from -3.4 x 1038 to 3.4 x 1038 and up to seven significant digits. Storage requirement is 4 bytes.
  • Double   Use for numeric floating point values that range from -1.797 x 10308 to 1.797 x 10308 and up to fifteen significant digits. Storage requirement is 8 bytes.
  • Replication ID   Use for storing a globally unique identifier required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format.
  • Decimal   Use for numeric values that range from -9.999... x 1027 to 9.999... x 1027. Storage requirement is 12 bytes.

 Tip   For best performance, always specify the smallest sufficient Field Size.

Format Determines the way that the field appears when it is displayed or printed in datasheets, or in forms or reports that are bound to the field. You can use any valid number format.
Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. For more information about creating indexes, see the See Also section.

Input Mask

Displays editing characters to guide data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.

Required

Requires that data be entered in the field.

Smart Tags Attaches a smart tag to the field.
Text Align

Specifies the default alignment of text within a control.

Validation Rule

Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text

Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page Top of Page

OLE Object

Purpose    Use to attach an OLE Object, such as a Microsoft Office Excel spreadsheet, to a record. If you want to use OLE (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) features, you must use the OLE Object data type.

In most cases, you should use an Attachment field instead of an OLE Object field. OLE Object fields support fewer file types than Attachment fields support. In addition, OLE Object fields do not allow you to attach multiple files to a single record.

Supported field properties

Property Use
Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Required

Requires that data be entered in the field.

Text Align

Specifies the default alignment of text within a control.

Top of Page Top of Page

Text

Purpose    Use to store up to 255 characters of text.

Supported field properties

Property Use
Allow Zero Length

Allows entry (by setting to Yes) of a zero-length string ("") in a Hyperlink, Text, or Memo field.

Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Default Value

Automatically assigns the specified value to this field when a new record is added.

Field Size

Enter a value from 1 to 255. Text fields can range from 1 to 255 characters. For larger text fields, use the Memo data type.

 Tip   For best performance, always specify the smallest sufficient Field Size.

For example, if you are storing postal codes of a known length, you should specify that length as the Field Size.

Format Determines the way that the field appears when it is displayed or printed in datasheets or in forms or reports that are bound to the field. You can define a custom format for a Text field. Find more information about defining a format in the See Also section.
IME Mode

Controls the conversion of characters in East Asian versions of Windows.

IME Sentence Mode

Controls the conversion of sentences in East Asian versions of Windows.

Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. For more information about creating indexes, see the See Also section.

Required

Requires that data be entered in the field.

Smart Tags Attaches a smart tag to the field.
Text Align

Specifies the default alignment of text within a control.

Unicode Compression

Compresses text that is stored in this field when less than 4,096 characters are stored.

Validation Rule

Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text

Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page Top of Page

Yes/No

Purpose    Use to store a Boolean value.

Supported field properties

Property Use
Caption

The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

 Tip   An effective caption is usually brief.

Default Value

Automatically assigns the specified value to this field when a new record is added.

Format

Determines the way that the field appears when it is displayed or printed in datasheets, or in forms or reports that are bound to the field. Select one of the following:

  • True/False   Displays the value as either True or False.
  • Yes/No   Displays the value as either Yes or No.
  • On/Off   Displays the value as either On or Off.
Indexed

Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

  • Yes (No duplicates)   Creates a unique index on the field.
  • Yes (Duplicates OK)   Creates a non-unique index on the field.
  • No   Removes any index on the field.

 Note   Do not change this property for a field that is used in a primary key.

Although you can create an index on a single field by setting the Indexed field property, some types of indexes cannot be created in this way. For example, you cannot create a multi-field index by setting this property. For more information about creating indexes, see the See Also section.

Text Align

Specifies the default alignment of text within a control.

Validation Rule

Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text

Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

Top of Page Top of Page

 
 
Applies to:
Access 2007