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.
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.
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 Type
Usage
Size
Text
Text 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.
Memo
Lengthy 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.
Number
Numeric 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/Time
Dates and times.
8 bytes
Currency
Currency 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
AutoNumber
Unique 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/No
Fields that will contain only one of two
values, such as Yes/No, True/False, On/Off.
1 bit
OLE Object
Objects (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).
Hyperlink
A UNC path or a URL path.
Up to
64,000 characters.
Lookup Wizard
Creates 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).
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 Property
Description
Field Size
You 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.
Format
You 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 Mask
You 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.
Caption
You 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 Value
You 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 Rule
You 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 Text
You can specify a message that must
appear when a validation rule is violated.
Required
You 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 Length
You can use the
AllowZeroLength property to specify if a zero-length string ("
") is a valid entry in a table field.
Indexed
You 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 Compression
Microsoft 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 Tags
In 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 Places
You can use the Decimal
Places property to specify the number of decimal places to display numbers.
New Values
You 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.
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
(http://support.microsoft.com/kb/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:
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.
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.
Back to the top
