Part 5 of "Keeping Your Information Accurate" in Access 2003 and Access 2002: "Using Validation Rules to Restrict Data" (311172)

MORE INFORMATION

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 on 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 can use 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. Follow these steps:

1. Open the Field Test database that you created in Part 2 of this series of articles. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

   311168 Keeping Your Information Accurate: Part 2: Using the Data Type to Restrict Data

2. Open the Field Property Test table in Design view.
3. To add a validation rule to PhoneField that will prevent the entry of an area code other than 206 or 425, start by selecting PhoneField and clicking in the Validation Rule box.
4. 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. Type the following in the Validation Rule box, and press ENTER:

   Like "206*" Or Like "425*"

5. In the Validation Text box, type Area code must be 206 or 425.
   
   You have set a rule for the first three digits typed in the PhoneField field and provided the text that Access should display if someone attempts to enter an invalid phone number.
6. Click in the Caption box, and type Phone Number.
   
   The table window now looks like this:
   
   
7. 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.
8. 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.
9. Return to Datasheet view, where the caption for the first field is now Phone Number.
10. Place the insertion point to the left of the first # of any Phone Number field, type 3605550009, and press ENTER.
    
    TIP: You can move the pointer to the left end of the Phone Number field and, when the pointer changes to a fat cross, click to select the entire 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.
11. Click OK to close the alert box, and type a new phone number with one of the allowed area codes.
12. 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.
13. Add a table validation rule to ensure that the second date is always later than the first one. Right-click the table window, and click Properties on the shortcut menu to open the Table Properties dialog box, shown here:
    
    
    
    TIP: This dialog box is not the one you see if 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.
14. Click in the Validation Rule box, type [DateField] <[Date2], and press ENTER.
15. Type Date2 must be later than DateField, and close the dialog box.
16. Save the table (click No to close the data integrity alert box), and return to Datasheet view.
17. In any record, type 6/1/01 in DateField and 5/1/01 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.
18. Click OK, change Date2 to 6/2/2001, and click in another record.
19. Close the table and the database.

ADDITIONAL RESOURCES