Microsoft Office Tutorials and References
In Depth Information
To create and test a table validation rule in the tblContract table:
1. Open the Navigation Pane, open the tblContract table in Design view, and then, in
the Show/Hide group on the Table Tools Design tab, click the Property Sheet but-
ton to open the property sheet for the table.
To make sure that each SigningDate field value is chronologically earlier than, or
less than, each StartDate field value, you’ll compare the two field values in the
Validation Rule text box.
2. In the property sheet, click the Validation Rule text box, type
[SigningDate]<[StartDate] , and then press the Tab key.
3. Type The signing date must be earlier than the start date in the Validation Text
text box. See Figure 5-42.
Make sure you type the
brackets to enclose the
field names. If you omit
the brackets, Access auto-
matically inserts quotation
marks around the field
names, in effect treating
the field names as field
values, and the table vali-
dation rule will not work
Setting table validation properties
properties for the table
only part of the
entered text is
You can now test the validation properties.
4. Close the property sheet, save the table, and then click the Yes button when asked
if you want to test the existing dates in the tblContract table against the new vali-
5. Close the Navigation Pane, switch to Datasheet view, click the Signing Date column
value in the first record, click the date picker icon to the right of the date, click 27
in the calendar control to change the date to 2/27/2010, press the Tab key to
advance to the Start Date column, and then press the Tab key two more times to
complete your changes to the record. A dialog box opens containing the message
“The signing date must be earlier than the start date,” which is the Validation Text
property setting you entered in Step 3.
Unlike field validation rule violations, which Access detects immediately after you
finish your field entry and advance to another field, Access detects table validation
rule violations when you finish all changes to the current record and advance to
6. Click the OK button, and then press the Esc key to undo your change to the
Signing Date column value.
7. Close the tblContract table.
Based on a request from Sarah, Lucia added a Memo field to the tblCustomer table,
and now you’ll review Lucia’s work.