MS-Access / Getting Started

---

Setting Field Properties for Web Databases

You can customize the way Access 2010 stores and handles each field in web databases by setting specific properties. These properties vary according to the data type you choose and are available in the Properties, Formatting, and Field Validation groups on the Fields contextual ribbon tab. Table-2 lists all the possible properties that can appear for fields in a web database displayed in Datasheet view, and the data types that are associated with each property.

Table-2 Field Properties on the Fields Contextual Tab

Property	Data Type	Options, Description
Field	Text	Text can be from 0 through 255 characters long, with a default length of 255 characters.
Format	Number	General Number (default). No commas or currency symbols; the number of decimal places shown depends on the precision of the data. Standard. Two decimal places and separator commas. Percent. Moves displayed decimal point two places to the right and appends a percentage (%) symbol.
	Currency	Currency.1 Currency symbol (from Regional And Language Options in the Control Panel) and two decimal places. Euro. Euro currency symbol (regardless of Control Panel settings) and two decimal places.
	Date/Time	General Date (default). Combines Short Date and time (for example, 7/1/2010 5:30:10 PM). Short Date.2 Uses Short Date Style from Regional And Language Options (for example, 7/1/2010).
	Calculated	Format property options for calculated fields depend on the Result Type. The format property options and defaults for the Result Type align with the other data types.
Increase/Decrease Decimals	Number and Currency	You can specify the number of decimal places that Access displays. The default specification is to display two decimal places for the Currency, 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 request a fixed display of decimal places by clicking these buttons.
Caption	All	You 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 Value	Text, Number, Currency, Date/Time, and Boolean	You 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 Rule	Text, Number, Currency, and Date/Time	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 "New York" Or "Seattle" Or "Chicago". In addition, you can specify a complex expression that includes any of the built-in functions in Access.
Validation Text	Text, Number, Currency, and Date/Time	You can specify a custom message that Access displays whenever the data entered does not pass your validation rule.
Required	All except Boolean and Calculated	If you don't want to allow a Null value in this field, select this property.
Unique	Text, Number, Currency, and Date/Time	If you don't want to allow duplicates in this field, select this property.
Indexed	Text, Number, Currency, and Date/Time	You can ask that an index be built to speed access to data values.
Memo Settings	Memo	Plain Text (default). The text in the Memo field is stored and displayed as plain text. Append Only. You can specify to see column history for this Memo field. When you change the memo field's data, the data change and time stamp are recorded and appended to the version history of the field. 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.
Modify Expression	Calculated	The 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.

1 Note that Currency and Euro 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.

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