Using Validation Rules to Restrict Data

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 FieldTest database in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\Accurate\ValRules folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the FieldTest database.

1. Open the Field Property Test table in Design view.

2. To add a validation rule to PhoneField that will prevent the entry of an area code other than 206 or 425, select PhoneField , and click in the Validation Rule box.

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

3. Type the following in the Validation Rule box, and press [ENTER]:

   Like "206*" Or Like "425*"

   Troubleshooting  

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

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

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

5. Click in the Caption box, and type Phone Number .

   

6. Save the table.

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

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

   Tip  

   You can test the validation rules in a table at any time by right-clicking the title bar of the table and clicking Test Validation Rules on the shortcut menu.

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

9. Place the insertion point to the left of the first # of any Phone Number field, type 3605550009 , and press [ENTER].

   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.

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

11. 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 DateField .

12. Right-click the table window, and click Properties on the shortcut menu to open the Table Properties dialog box.

    

    Tip  

    This dialog box is not the same one displayed when you right-click the table in the database window and click Properties. The only point in common between the two is the Description property, which you can enter in either dialog box.

13. Click in the Validation Rule box, type [DateField] < [Date2] , and press [ENTER].

14. Type Date2 must be later than DateField , and close the dialog box.

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

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

16. In any record, type 6/1/03 in DateField and 5/1/03 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 DateField.

17. Click OK , change Date2 to 6/2/2003 , and click in another record.

18. Close the table.

CLOSE the FieldTest database.