MS-Access / Getting Started

Setting Field Properties

You can customize the way Access 2010 stores and handles each field by setting specific properties. These properties vary according to the data type you choose. Table-2 lists all the possible properties that can appear on a field's General tab in a table's Design view, and the data types that are associated with each property.

Table-2 Field Properties on the General Tab
PropertyData TypeOptions, Description
Field SizeTextText can be from 0 through 255 characters long, with a default length of 255 characters.
NumberByte. A 1-byte integer containing values from 0 through 255.
Integer. A 2-byte integer containing values from -32,768 through +32,767.
Long Integer. A 4-byte integer containing values from -2,147,483,648 through +2,147,483,647.
Single.1 A 4-byte floating-point number containing values from -3.4 x 1038 through +3.4 x 1038 and up to seven significant digits.
Double.1 An 8-byte floating-point number containing values from -1.797 x 10308 through +1.797 x 10308 and up to 15 significant digits.
Replication ID.2 A 16-byte globally unique identifier (GUID).
Decimal. A 12-byte integer with a defined decimal precision that can contain values from approximately -7.9228x1028 through +7.9228x1028. The default precision (number of decimal places) is 0 and the default scale is 18.
New ValuesAutoNumber onlyIncrement. Values start at 1 and increment by 1 for each new row.
Random. Access assigns a random long integer value to each new row.
FormatText, MemoYou can specify a custom format that controls how Access displays the data. For details about custom formats, Access Help topic "Format Property-Text and Memo Data Types."
Number (except Replication ID), Currency, AutoNumberGeneral Number (default). No commas or currency symbols; the number of decimal places shown depends on the precision of the data.
Currency.3 Currency symbol (from Regional And Language Options in Windows Control Panel) and two decimal places.
Euro. Euro currency symbol (regardless of Control Panel settings) and two decimal places.
Fixed. At least one digit and two decimal places.
Standard. Two decimal places and separator commas.
Percent.Moves displayed decimal point two places to the right and appends a percentage (%) symbol.
Scientific. Scientific notation (for example, 1.05E+06 represents 1.05 x 106).
You can specify a custom format that controls how Access displays the data. For details about custom formats, the Access Help topic "Format Property-Number and Currency Types."
Date/Time4General Date (default). Combines Short Date and Long Time formats (for example, 7/1/2010 5:30:10 PM).
Long Date. Uses Long Date Style from the Regional And Language Options item in Control Panel (for example, Thursday, July 1, 2010).
Medium Date. 1-Jul-2010.
Short Date.5 Uses Short Date Style from the Regional And Language Options item (for example, 7/1/2010).
Long Time. Uses Time Style from the Regional And Language Options item (for example, 5:30:10 PM).
Medium Time. 5:30 PM.
Short Time. 17:30.
Yes/NoYes/No (default)
True/False
On/Off
You can specify a custom format that controls how Access displays the data. For details about custom formats, see "Setting Control Properties for Client Forms," the Access Help topic "Format Property-Yes/No Data Type."
CalculatedFormat options for calculated fields depend on the Result Type. The format options and defaults for the Result Type align with the other data types.
PrecisionNumber, DecimalYou can specify the maximum number of digits allowed. The default value is 18, and you can specify an integer value between 1 and 28.
ScaleNumber, DecimalYou can specify the number of digits stored to the right of the decimal point. This value must be less than or equal to the value of the Precision property.
Decimal PlacesNumber (except Replication ID), Currency, CalculatedYou can specify the number of decimal places that Access displays. The default specification is Auto, which causes Access to display two decimal places for the Currency, Fixed, Standard, and Percent formats and the number of decimal places necessary to show the current precision of the numeric value for General Number format. You can also request a fixed display of decimal places ranging from 0 through 15.
Input MaskText, Number (except Replication ID), Date/Time, CurrencyYou can specify an editing mask that the user sees while entering data in the field. For example, you can have Access provide the delimiters in a date field such as __/__/__, or you can have Access format a U.S. phone number as (###) 000-0000.
CaptionAllYou can enter a more fully descriptive field name that Access displays in form labels and in report headings. (Tip: If you create field names with no embedded spaces, you can use the Caption property to specify a name that includes spaces for Access to use in labels and headers associated with this field in queries, forms, and reports.)
Default ValueText, Memo, Number, Date/Time, Currency, Hyperlink, and Yes/NoYou can specify a default value for the field that Access automatically uses for a new row if no other value is supplied. If you don't specify a Default Value, the field will be Null if the user fails to supply a value. (See also the Required property.)
Validation RuleAll (except OLE Object, Replication ID, Attachment, Calculated, and AutoNumber)You can supply an expression that must be true whenever you enter or change data in this field. For example, <100 specifies that a number must be less than 100. You can also check for one of a series of values. For example, you can have Access check for a list of valid cities by specifying "Glasgow" Or "Manchester" Or "Liverpool". In addition, you can specify a complex expression that includes any of the built-in functions in Access.
Validation TextAll (except OLE Object, Replication ID, Attachment, Calculated, and AutoNumber)You can specify a custom message that Access displays whenever the data entered does not pass your validation rule.
RequiredAll (except Calculated and AutoNumber)If you don't want to allow a Null value in this field, set this property to Yes.
Allow Zero LengthText, Memo, HyperlinkYou can set the field equal to a zero-length string ("") if you set this property to Yes.
IndexedAll except OLE Object, Calculated, and AttachmentYou can ask that an index be built to speed access to data values. You can also require that the values in the indexed field always be unique for the entire table.
Unicode CompressionText, Memo, HyperlinkAs of version 2000, Access stores character fields in an .mdb and .accdb file using a double-byte (Unicode) character set to support extended character sets in languages that require them. The Latin character set required by most Western European languages (such as English, Spanish, French, or German) requires only 1 byte per character. When you set Unicode Compression to Yes for character fields, Access stores compressible characters in 1 byte instead of 2, thus saving space in your database file. However, Access will not compress Memo or Hyperlink fields that will not compress to fewer than 4,096 bytes. The default for new tables is Yes in all countries where the standard language character set does not require 2 bytes to store all the characters.
IME Mode, IME Sentence ModeText, Memo, HyperlinkOn machines with an Asian version of Windows and appropriate Input Method Editor (IME) installed, these properties control conversion of characters in kanji, hiragana, katakana, and hangul character sets.
Smart TagsAll data types except Yes/No, OLE Object, Attachment, and Replication IDIndicates the registered smart tag name and action that you want associated with this field. When the user views this field in a table datasheet, a query datasheet, or a form, Access displays a smart tag available indicator next to the field. The user can click on the indicator and select the smart tag action to perform.
Text AlignAll data types except AttachmentGeneral (default). Text aligns to the left, but numbers and dates align to the right.
Left. All data aligns to the left.
Center. All data aligns to the center of the field.
Right. All data aligns to the right.
Distribute. The data is evenly distributed throughout the field.
Text FormatMemo onlyPlain Text (default). The text in the Memo field is stored and displayed as plain text.
Rich Text. You can specify that the data in the Memo field can be formatted as rich text. Access applies HTML formatting tags to your data.
Append OnlyHyperlink and MemoYou can specify to see column history for this field. When you change the field's data, the data change and time stamp are recorded and appended to the version history of the field.
Show Date PickerDate/Time onlyFor Dates (default). Displays the built-in date picker control to select a date when the field receives focus in a table datasheet or query.
Never. The built-in date picker control is not shown when the field receives focus in a table datasheet or query.
ExpressionCalculatedThe expression used to calculate the value for this column. The expression can use the value of one or more fields in the same table and can be up to 65,000 characters in length.
Result TypeCalculatedFor calculated fields, you need to provide the data type that results from the expression you use for the field. The result type can be Double, Integer, Long Integer, Single, Replication ID, Decimal, Text, Date/Time, Memo, Currency, or Yes/No.

1 Single and Double field sizes use an internal storage format called floating point, which can handle very large or very small numbers, but which is somewhat imprecise. If the number you need to store contains more than 7 significant digits for a Single or more than 15 significant digits for a Double, the number will be rounded. For example, if you try to save 10,234,567 in a Single, the actual value stored will be 10,234,570. Likewise, Access stores 10.234567 as 10.23457 in a Single. If you want absolute fractional precision, use Decimal field size instead.

2 In general, you should use the Replication ID field size only in an Access 2003 format and earlier database that is managed by the Replication Manager.

3 Note that Currency, Euro, Fixed, and Standard formats always display two decimal places regardless of the number of actual decimal places in the underlying data. Access rounds any number to two decimal places for display if the number contains more than two decimal places.

4 You can also specify a custom format in addition to the built-in ones described here.

5 To help alleviate problems with dates spanning the start of the century, we recommend that you select the Use Four-Digit Year Formatting check box in Access. To do this, click the File tab on the Backstage view, click Options, and then scroll to the General section in the Client Settings category to find this option. You should also be sure that your Short Date Style in the Regional And Language Options dialog box uses a four-digit year. (This is the default in Windows XP, Windows Vista, and Windows 7; you can double-check your settings by accessing Regional And Language Options within Control Panel.)

If you specify a validation rule but no validation text, Access 2010 generates an ugly and cryptic message that your users might not understand:

"One or more values are prohibited by the validation rule '<your expression here>' set for '<table name.field name>'. Enter a value that the expression for this field can accept."

Unless you like getting lots of support calls, we recommend that you always enter a custom validation text message whenever you specify a validation rule.

[Previous] [Contents] [Next]

In this tutorial:

  1. Designing Client Tables
  2. Creating a New Database
  3. Creating a New Empty Database
  4. Creating Your First Simple Table by Entering Data
  5. Creating a Table Using Application Parts
  6. Creating a Table Using Data Type Parts
  7. Creating a Table in Design View
  8. Understanding Field Data Types
  9. Setting Field Properties
  10. Nulls and Zero-Length Strings
  11. Defining Simple Field Validation Rules
  12. Defining Input Masks
  13. Defining a Primary Key
  14. Defining a Table Validation Rule
  15. Understanding Other Table Properties
  16. Defining Relationships
  17. Defining Your First Relationship
  18. Creating a Relationship on Multiple Fields
  19. Adding Indexes
  20. Multiple-Field Indexes
  21. Setting Table Design Options
  22. Creating a Default Template for New Databases
  23. Printing a Table Definition
  24. Database Limitations