Restricting Data by Using Validation Rules

A validation rule is an expression that can precisely define the information that will be accepted in one or several fields in a record. You might use a validation rule in a field containing the date an employee was hired to prevent a date in the future from being entered. Or if you make deliveries to only certain local areas, you could use a validation rule on the phone field or ZIP code field to refuse entries from other areas.

You can type validation rules in by hand, or you can use the Expression Builder to create them. At the field level, Access uses the rule to test an entry when you attempt to leave the field. At the table level, Access uses the rule to test the content of several fields when you attempt to leave the record. If an entry doesn’t satisfy the rule, Access rejects the entry and displays a message explaining why.

In this exercise, you will create and test several field validation rules and one table validation rule.

Use the 04_Validate database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.

Open the 04_Validate database. Then display the Field Property Test table in Design view.

1. Select fPhone, and then click in the Validation Rule box.

An ellipsis button appears at the end of the Validation Rule box. You can click this button to use the Expression Builder to create an expression, or you can type an expression in the box.

2. Type Like “206*” Or Like “425*” in the Validation Rule box, and press .

Troubleshooting 

Be sure to include the asterisk after the 206 and 425.

3. In the Validation Text box, type Area code must be 206 or 425.

A rule is set for the first three digits typed in the fPhone field including the text that Access should display if someone attempts to enter an invalid phone number.

4. In the Caption box, type Phone Number.

5. Save the table.

Access warns that data integrity rules have changed. The table violates the new rule because it contains blank phone number fields.

6. Click No to close the message box without testing the data.

Tip 

When displaying tables as overlapping windows (rather than tabbed documents), you can test the validation rules in a table at any time by right-clicking the title bar of the table in Design view and then clicking Test Validation Rules.

7. Return to Datasheet view, where the caption for the first field is now Phone Number.

8. Place the insertion point to the left of the first # of any Phone Number field, type 3605550109, and then press .

Tip 

To select the entire field, move the pointer to the left end of the Phone Number field, and when the pointer changes to a thick cross, click the field. The insertion point is then at the start of the area code when you begin typing.

The Validation Rule setting causes Access to display an alert box, warning you that the area code must be either 206 or 425.

9. Click OK to close the alert box, type a new phone number with one of the allowed area codes, and press .

10. Return to Design view, and add another date field. Type Date2 as the field name, set the data type to Date/Time, and drag the new field to just below fDate.

11. Right-click in the table window, and then click Properties.

Tip 

The purpose of this Property Sheet is to set properties that apply to more than one field in the table, as opposed to setting properties for a single field in the Field Properties area.

12. Click in the Validation Rule box, type [Date2]>[fDate], and press .

13. In the Validation Text box, type Date2 must be later than fDate, and then close the sheet.

A table validation rule is added that ensures that the second date is always later than the first one.

14. Save the table (click No to close the data-integrity alert box), and return to Datasheet view.

15. In any record, type 6/1/07 in fDate and 5/1/07 in Date2, and then click in another record.

Access displays the Validation Text setting from the Table Properties dialog box, reminding you that Date2 must be later than fDate.

16. Click OK, change Date2 to 6/2/2007, and then click in another record.

Close the Field Property Test table and the 04_Validate database.