Microsoft Access 2010 Bible

Chapter 2: Creating Access Tables

69

Captions can be as long as 2,048 characters, more than adequate for all but the most verbose

descriptions.

Validation Rule and Validation Text

The Validation Rule property establishes requirements for input into the field. Enforced by

the Jet database engine, the Validation Rule ensures that data entered into the table conforms

to the requirements of the application.

Validation properties are a great way to enforce business rules, such as ensuring that a product is not

sold for zero dollars, or requiring that an employee review date comes after her hire date. And, like

other field properties, validation rules are enforced wherever the field is used in the application.

The value of the Validation Rule property is a string containing an expression that is used to

test the user’s input. The expression used as a field’s Validation Rule property can’t contain

user-defined functions or any of the Access domain or aggregate functions (DCount, DSum, and so

on). A field’s Validation Rule property can’t reference forms, queries, or other tables in the

application. (These restrictions don’t apply to validation rules applied to controls on a form, how-

ever.) Field validation rules can’t reference other fields in the table, although a rule applied to a

record in a table can reference fields in the same table (a record-level validation rule is set in the

table’s Property Sheet, rather than on an individual field).

The Validation Text property contains a string that is displayed in a message box when the

user’s input doesn’t satisfy the requirements of the Validation Rule property. The maximum

length of the Validation Text property value is 255 characters.

When using the Validation Rule property, you should always specify a Validation Text

value to avoid triggering the generic message box Access displays when the rule is violated. Use the

Validation Text property to provide users with a helpful message that explains acceptable val-

ues for the field. Figure 2.20 shows the message box displayed when the value specified by the

Validation Rule attached to the CreditLimit field is exceeded.

FIGURE 2.20

A data-validation warning box. This appears when the user enters a value in the field that does not match
the rule specified in the design of the table.

The Validation Rule property doesn’t apply to check boxes, option buttons, or toggle buttons

within an option group on a form. The option group itself has a Validation Rule property that

applies to all the controls within the group.

Validation properties are often used to ensure that certain dates fall after other dates (for example,

an employee’s retirement date must fall after his starting date), that nonnegative numbers are