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