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

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.

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

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.

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.