Information about data types and field properties that can be used in an Access database

Article translations Article translations
Article ID: 824263 - View products that this article applies to.
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

On This Page

SUMMARY

This article discusses the different data types that are available for you to use for the fields in a table in a Microsoft Office Access database. This article also discusses the field properties that are available for you to use when you design a table.

MORE INFORMATION

Decide what data type to use for the field in a table

You must decide the data type that you will use for the field. The data type that you select must be based on the following considerations:
  • What kind of value do you want to permit in the field?

    You cannot store text in a field that has a Number data type.
  • How much storage space do you want to use for the value that is in the field?

    Some data types require more storage space than others.
  • What types of operations do you want to perform on the value that is in the field?

    Access can sum values in a Number field or in a Currency field. Access cannot sum values in a Text field or in an OLE Object field.
  • Do you want to sort the field or to index the field?

    You cannot sort or index OLE Object fields.
  • Do you want to use the field to group records in queries or in reports?

    You cannot use OLE Object fields to group records.
  • How do you want to sort values in the field?

    If you put numbers in a Text field, the numbers are sorted as strings of characters (1, 10, 100, 2, 20, 200), not as numeric values. Use a Number field or a Currency field to sort numbers as numeric values. Also, many date formats cannot be sorted correctly if they are entered in a Text field. Use a Date/Time field to make sure that dates are sorted correctly.

Information about data types

The following table summarizes all the field data types that are available in Access, the usage of the data type, and also the storage size for each data type.

Collapse this tableExpand this table
Data TypeUsageSize
TextText or combinations of text and numbers in the field, such as addresses.

Numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.
Up to 255 characters.

Note: Access stores only the characters that are entered in the field. Access does not store space characters for unused positions in a Text field. To control the maximum number of characters that can be entered in the field, set the Field Size property to the value that you want.
MemoLengthy text and numbers, such as notes or descriptions.Up to 64,000 characters for Access 97, Access 2000, and Access 2002. Up to 65,536 characters for Access 2003.
NumberNumeric data that can be used for mathematical calculations, except calculations involving money (use Currency type). Set the Field Size property to define the specific Number type. 1, 2, 4, or 8 bytes. 16 bytes for Replication ID (GUID) only.
Date/TimeDates and times.8 bytes
CurrencyCurrency values. Use the Currency data type to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right of the decimal point. 8 bytes
AutoNumberUnique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. 4 bytes. 16 bytes for Replication ID (GUID) only.
Yes/NoFields that will contain only one of two values, such as Yes/No, True/False, On/Off.1 bit
OLE ObjectObjects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that are created in other programs by using the OLE protocol that can be linked to or embedded in an Access table. You must use a bound-object frame in a form or in a report to display the OLE object.Up to 1 gigabyte (limited by disk space).
HyperlinkA UNC path or a URL path.Up to 64,000 characters.
Lookup WizardCreates a field that permits you to pick a value from another table or from a list of values by using a combo box. When you select the Lookup Wizard in the data type list, a wizard starts automatically so that you can define the lookup field.The same size as the primary key field that is also the Lookup field (typically 4 bytes).


Information about field properties

You can control how you want to store, to handle, and to display data in a field. To do this, you can use a set of field properties that correspond to the field. For example, you can control the maximum number of characters that can be entered in a Text field by setting the Field Size property for the field. You can set the properties for the fields in a table in Design view. You can select the field in the upper portion of the window, and then select the property that you want for the field in the lower portion of the window.

The properties that are available for each field are determined by the data type that you select for the field. The following table lists the available field properties in an Access database. If a certain property does not appear in the property sheet for a field, the property is not available for the data type of that field.

Collapse this tableExpand this table
Field PropertyDescription
Field SizeYou can use the Field Size property to set the maximum size for data that is stored in a field that is set to the Text data type, the Number data type, or the AutoNumber data type.
FormatYou can use the Format property to customize the way that numbers, dates, times, and text appear and print. The Format property only controls how the information in the field appears. The property does not store the information as formatted.
Input MaskYou can use the Input Mask property to make data entry easier and to control the values that users can enter in a Text Box control.
CaptionYou can use the Caption property to provide helpful information to the user through captions on objects in various views:

Field captions specify the text for labels that are attached to controls. You can create the field caption by moving the field from the field list. The field caption acts as the column heading for the field in a table or in a query in Datasheet view.

Form captions specify the text that appears in the title bar in Form view.

Form captions specify the text that appears in the title bar in Form view.

Button captions and label captions specify the text that appears in the control.
Default ValueYou can specify a value for a field that is automatically entered in the field when a new record is created. For example, in an Addresses table, you can set the default value for the City field to New York. When you add a record to the table, you can either accept this value or you can enter the name of a different city.
Validation RuleYou can use the Validation Rule property to specify requirements for data that are entered in a record, in a field, or in a control. When data is entered that violates the Validation Rule setting, you can use the Validation Text property to specify the message that you want to appear when the violation occurs.
Validation TextYou can specify a message that must appear when a validation rule is violated.
RequiredYou can use the Required property to specify if a value is required in a field. If this property is set to Yes, when you enter data in a record, you must enter a value in the field or in any control that is bound to the field, and the value cannot be Null. For example, you may want to make sure that a LastName control has a value for each record. When you want to permit Null values in a field, you must not only set the Required property to No, but if there is a Validation Rule property setting, that setting must also explicitly state validationrule Or Is Null.
Allow Zero LengthYou can use the AllowZeroLength property to specify if a zero-length string (" ") is a valid entry in a table field.
IndexedYou can use the Indexed property to set a single-field index. An index speeds up queries on the indexed fields. An index also speeds up sorting operations and grouping operations on the indexed fields. For example, if you search for specific employee names in a LastName field, you can create an index for this field to speed up the search for a specific name.
Unicode CompressionMicrosoft Access 2000 and later use the Unicode character-encoding scheme to represent the data in a Text field, in a Memo field, or in a Hyperlink field. Unicode represents each character as two bytes. Therefore, the data that is in a Text field, in a Memo field, or in a Hyperlink field requires more storage space than is required in Microsoft Access 97 and earlier. In Access 97 and earlier, each character is represented as one byte.

To offset the effect of Unicode character representation and to make sure performance is optimized, the default value of the Unicode Compression property for a Text field, a Memo field, or a Hyperlink field is set to Yes. When the Unicode Compression property for a field is set to Yes, any character whose first byte is 0 is compressed when it is stored and then uncompressed when it is retrieved. Because the first byte of a Latin character (a character of a Western European language such as English, Spanish, or German) is 0, Unicode character representation does not affect how much storage space is required for compressed data that consists completely of Latin characters.
Smart TagsIn Access 2003, you can use the Smart Tags property to add the available smart tags to the field. If you add smart tags to a field, each value that is specified for the field is analyzed. If the value is recognized as one of the specified smart tags, you can perform various actions as defined by the smart tag for the value of the field.
Decimal PlacesYou can use the Decimal Places property to specify the number of decimal places to display numbers.
New ValuesYou can use the New Values property to specify how AutoNumber fields increment when new records are added to a table. Only AutoNumber fields can use the New Values property.


For additional information about any field property, you can move the focus to the property in Design view for the table, and then press F1. The Help topic for the property appears in the new help window.

REFERENCES

For more information about creating tables in an Access database, click the following article number to view the article in the Microsoft Knowledge Base:
304238 How to create a table in an Access database
The following are some additional references information based on the version of Access you are using:

Access 97, Access 2000, and Access 2002

For more information about working with field properties, click Microsoft Access Help on the Help menu, type field properties in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Access 2003

For more information about how to set the data types or the properties for a field, click Microsoft Office Access Help on the Help menu, type Set or change the data type or the size for a field (.mdb) in the Search for box in the Assistance pane, and then click Start searching to view the topic.

Properties

Article ID: 824263 - Last Review: September 17, 2011 - Revision: 2.0
APPLIES TO
  • Microsoft Access 97 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 2002 Standard Edition
  • Microsoft Office Access 2003
Keywords: 
kbdesign kbfield kbdatabase kbproperties kbinfo KB824263

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com