Microsoft Office Online
Sign in to My Office Online (What's this?) | Sign in

 
 
Microsoft Office Access
Search
Search
 
Icon: Flag: (c) Microsoft
Get up to speed
Icon: Business: (c) Microsoft
Your business
Updates: (c) Microsoft
Office updates
 
 
 
Product Information
Help and How-to
Choose your version
Training
Templates
Support and Feedback
Technical Resources
Additional Resources
Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
Printer-Friendly VersionPrinter-Friendly Version
Field properties quick reference
 

This article provides quick reference information about data types and other field properties.

For more information about data types, field properties, and fields, refer to the See Also section.

In this article

Data types

The following table provides a list of the available data types in Microsoft Office Access 2007, along with usage guidelines and storage capacities for each type.

Data type Use Size
Text Use for alphanumeric characters, including text, or text and numbers, that are not used in calculations (for example, a product ID). Up to 255 characters
Memo Use for text greater than 255 characters in length, or for text that uses rich text formatting. Examples include notes, lengthy descriptions, and paragraphs that use text formatting, such as bold or italics.

Use the Text Format property of a Memo field to specify whether the field supports formatted text.

Set the Append Only property of a Memo field to Yes to retain previous versions of the field value when the value changes.

 Up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in any single control.

 Note   The maximum size for an Office Access 2007 database file is 2 gigabytes.
Number Use for storing numeric values (integers or fractional) that will be used in calculations, except for monetary values.

 Note   Use the Currency data type for monetary values.

 1, 2, 4, 8, or 12 bytes (16 bytes when used for a replication ID)

For more information, refer to the Number Field Size entry in the Field properties reference table.
Date/Time Use for storing date and time values. Note that each stored value includes both a date component and a time component. 8 bytes
Currency

Use for storing monetary values (currency).

 8 bytes
AutoNumber Use for generating unique values that can be used as a primary key, which Access inserts when a record is added. Note that AutoNumber fields can be incremented sequentially or by a specified increment, or assigned randomly. 4 bytes (16 bytes when used for replication ID)
Yes/No Use for Boolean values: Yes/No, True/False, or On/Off. 1 bit (0.125 bytes)
OLE Object Use for storing OLE objects from other Microsoft Windows programs. Up to 1 gigabyte
Attachment Use for storing binary files (that is, files that you cannot read by using a text editor), such as digital images (photos and graphics) or files created by using other Microsoft Office products.

You can attach more than one file per record to an Attachment field.

 For compressed attachments, 2 gigabytes. For uncompressed attachments, approximately 700kb, depending on the degree to which the attachment can be compressed.

 Note   The maximum size for an Office Access 2007 database file is 2 gigabytes.
Hyperlink Use for storing hyperlinks, which provide single-click access to Web pages through a URL (Uniform Resource Locator) or to files through a name in UNC (universal naming convention) format. You can also link to Access objects that are stored in a database. Up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in any single control.

 Note   The maximum size for an Office Access 2007 database file is 2 gigabytes.
Lookup Wizard Use to start the Lookup Wizard so that you can create a field that uses a combo box to look up a value in another table, query, or list of values. Note that Lookup Wizard is not an actual data type. If the lookup field is bound to a table or a query, the size of the bound column.

If the lookup field is not bound to another column (and stores a list of values), the size of the Text field used to store the list.

Top of Page Top of Page

Automatic data type detection examples

You can create a new field in Datasheet view by typing in an empty column. Office Access 2007 automatically assigns a data type to each field that you create in this way. The following table shows how automatic data type detection works.


If you enter: Office Access 2007 creates a field with a data type of:
John Text
http://www.contoso.com

You can use any valid Internet protocol prefix. For example, http://, https://, and mailto: are valid prefixes.

 Hyperlink
1 Number, Long Integer
50,000 Number, Long Integer
50,000.99 Number, Double
50000.389 Number, Double
12/67

The date and time formats recognized are those of your user locale.

 Date/Time
December 31, 2006 Date/Time
10:50:23 Date/Time
10:50 am Date/Time
17:50 Date/Time
$12.50

The currency symbol recognized is that of your user locale.

 Currency
21.75 Number, Double
123.00% Number, Double
3.46E+03 Number, Double

 Note   In addition to determining the data type, Access may automatically set the Format property for a new field, depending on what you type into that field. For example, if you type 10:50 am in the field, Access sets the data type to Date/Time and the Format property to Medium Time.

Top of Page Top of Page

Field properties

The following table lists field properties and their availability and effects by data type.

 Tip   To provide more space in which to enter or edit a property setting in the property box, press SHIFT+F2 to display the Zoom box. If you are entering an input mask or validation expression and want help in building it, click Button image next to the property box to display the appropriate builder tool.

Use this field
property...		 With these data types... To...
Field Size
  • Text
  • Number
  • AutoNumber
 Set the maximum size for data stored as a Text, Number, or AutoNumber data type.

More information about property settings for these data types:

Show Text

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.

 Note   For data in a Text field, Access does not reserve space beyond what is necessary to hold actual values. The Field Size property is the maximum field value size.

ShowNumber

Select one of the following:
  • Byte — For integers that range from 0 to 255. Storage requirement is a single byte.
  • Integer — For integers that range from -32,768 to +32,767. Storage requirement is two bytes.
  • Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647. Storage requirement is four bytes.

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

  • Single — 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 four bytes.
  • Double — For numeric floating point values that range from -1.797 x 10308 to +1.797 x 10308 and up to 15 significant digits. Storage requirement is eight bytes.
  • Replication ID — For storing a globally unique identifier that is required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format.
  • Decimal — For numeric values that range from -9.999... x 1027 to +9.999... x 1027. Storage requirement is 12 bytes.

ShowAutoNumber

Select one of the following:
  • Long Integer — For unique, numeric values that range from 1 to +2,147,483,648 when the New Values field property is set to Increment, and -2,147,483,648 to +2,147,483,647 when the New Values field property is set to Random. Storage requirement is four bytes.
  • Replication ID — 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.

 Tip   For best performance, always specify the smallest sufficient Field Size.
Format
  • Text
  • Memo
  • Number
  • Date/Time
  • Currency
  • AutoNumber
  • Yes/No
  • Hyperlink
 Customize the way that the field appears by default when it is displayed or printed.

More information about property settings for these data types:

ShowText

You can define a custom format for a Text field. For more information, see the article Format data in tables.

ShowNumber

Select one of the following:
  • General Number — Displays the number as it was entered.

    For example, 3456.789 is displayed as 3456.789.

  • Currency — Displays the number by using the thousand separator, and applies the settings in the Regional and Language Options in Control Panel for negative amounts, decimal and currency symbols, and decimal places.

    For example, 3456.789 is displayed as $3,456.79.

  • Euro — Displays the number using the Euro currency symbol, regardless of the symbol specified in the Regional and Language Options.

    • For example, 3456.789 is displayed as €3,456.79, and 3 456,789 becomes 3 456,79€.

  • Fixed — Displays at least one digit and applies the settings in the Regional and Language Options in Control Panel for negative amounts, decimal and currency symbols, and decimal places.

    For example, 3456.789 is displayed as 3456.79.

  • Standard — Displays the number by using the thousand separator and applies the settings in the Regional and Language Options in Control Panel for negative amounts, decimal symbols, and decimal places. This format does not display a currency symbol.

    For example, 3456.789 is displayed as 3,456.79.

  • Percent — Multiplies the value by 100 and displays the number with a percent sign appended to the end. Applies the settings in the Regional and Language Options in Control Panel for negative amounts, decimal symbols, and decimal places.

    For example, 0.3456 is displayed as 35%.

  • Scientific — Displays the value in standard scientific notation.

    For example, 3456.789 is displayed as 3.46E+03.

ShowDate/Time

Select one of the following predefined display formats:
  • General Date — Displays the value by using a combination of the Short Date and Long Time settings.
  • Long Date — Displays the value by using the Long Date setting of the Regional and Language Options in Control Panel.
  • Medium Date — Displays the value by using the format dd-mmm-yy (14-Jul-06, for example).
  • Short Date — Displays the value by using the Short Date setting of the Regional and Language Options in Control Panel.
  • Long Time — Displays the value by using the Time setting of the Regional and Language Options in Control Panel.
  • Medium Time — Displays the value by using the format HH:MM PM, where HH represents the hour, MM represents the minute, and PM represents either AM or PM. The hour value can range from 1 to 12. The minute value can range from 0 to 59.
  • Short Time — Displays the value using the format HH:MM where HH is the hour and MM is the minute. The hour can range from 0 to 23 and the minute from 0 to 59.

ShowYes/No

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.

  Notes  

  • True=Yes=On

    • and

  • False=No=Off
Decimal Places
  • Number
  • Currency
 Specify the number of decimal places to use when displaying numbers.
New Values
  • AutoNumber
 Set whether an AutoNumber field is incremented or assigned a random value when a new record is added.
Input Mask
  • Text
  • Number
  • Date/Time
  • Currency
 Display editing characters to guide data entry.
Caption All data types Set the text displayed by default in labels for forms, reports, and queries.
Default Value
  • Text
  • Memo
  • Number
  • Date/Time
  • Currency
  • Yes/No
  • Hyperlink
 Automatically assign the specified value to a field when a new record is added.
Validation Rule
  • Text
  • Memo
  • Number
  • Date/Time
  • Currency
  • Yes/No
  • Hyperlink
 Supply an expression that must be true to add or change the value in this field.
Validation Text
  • Text
  • Memo
  • Number
  • Date/Time
  • Currency
  • Yes/No
  • Hyperlink
 Enter text that appears when a value entered in this field violates the expression in the Validation Rule box.
Required All data types except AutoNumber Require that this field must contain a value in every record.
Allow Zero Length
  • Text
  • Memo
  • Hyperlink
 Allow entry (by setting to Yes) of a zero-length string ("") in a Text or Memo field.
Indexed
  • Text
  • Memo
  • Number
  • Date/Time
  • Currency
  • AutoNumber
  • Yes/No
  • Hyperlink
 Speed up read access to data in this field by creating and using an index.

 Note   The Memo and Hyperlink fields support indexes on only the first 255 characters of the field.

 Important   An index on a field causes append, delete, and update operations to take longer, because the index must be updated to reflect any changes.

For more information about indexes, refer to the See Also section.
Unicode Compression
  • Text
  • Memo
  • Hyperlink
 Compress the data in this field when fewer than 4,096 characters are stored (always true for a Text field). Has no effect when more than 4,096 characters are stored.
IME Mode
  • Text
  • Memo
  • Date/Time
  • Hyperlink
 Control conversion of characters in East Asian versions of Windows.
IME Sentence Mode
  • Text
  • Memo
  • Date/Time
  • Hyperlink
 Control conversion of sentences in East Asian versions of Windows.
Smart Tags
  • Text
  • Memo
  • Number
  • Date/Time
  • Currency
  • AutoNumber
  • Hyperlink
 Attach a smart tag (smart 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 a smart tag.) to the field.
Append Only
  • Memo
  • Hyperlink
 Track field value history by setting this property to Yes.

 Warning   Changing this property to No will erase field value history.

ShowMore information about Append Only

When you set Append Only to Yes, Access keeps track of changes to the field values.

You can view the history of an Append Only field by right-clicking a value in the field, and then clicking Show column history on the shortcut menu. Access displays a history of changes to the field value.
Text Format
  • Memo
 Select Rich Text to store field data as HTML and allow rich text formatting. Select Plain Text to store only text.
Text Align All data types except Attachment Specify the default alignment of text within a control.
Show Date Picker
  • Date/Time
 Specify whether Access displays a date picker (calendar control) when users edit a field value.

 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.

Top of Page Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.
See Also
advertisement
Printer-Friendly VersionPrinter-Friendly Version