|
|
|
|
|
|
|
|
|
|
|
| Microsoft® Access Version 2002 Step by Step |
|
|
|
|||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||
Chapter 6: Keeping Your Information Accurate continued
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 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. The working folder for this exercise is SBS\Access\Accurate\ValRules. Follow these steps:
- Open the Field Test database located in the working folder.
- Open the Field Property Test table in Design view.
- 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.
- Type the following in the Validation Rule box, and press Enter:
- In the Validation Text box, type Area code must be 206 or 425.
- Click in the Caption box, and type Phone Number.
- Save the table.
- Click No to close the message box without testing the data.
- Return to Datasheet view, where the caption for the first field is now Phone Number.
- Place the insertion point to the left of the first # of any Phone Number field, type 3605550009, and press Enter.
- Click OK to close the alert box, and type a new phone number with one of the allowed area codes.
- 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.
- 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:
- Click in the Validation Rule box, type [DateField]<[Date2], and press Enter.
- Type Date2 must be later than DateField, and close the dialog box.
- Save the table (click No to close the data-integrity alert box), and return to Datasheet view.
- In any record, type 6/1/01 in DateField and 5/1/01 in Date2, and then click in another record.
- Click OK, change Date2 to 6/2/2001, and click in another record.
- Close the table and the database.
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.
Like "206*" Or Like "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.
The table window now looks like this:
Click to view graphic
Access warns you that data integrity rules have changed. The table violates the new rule because it contains blank phone number fields.
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.
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.
Click to view graphic
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.
Access displays the Validation Text setting from the Table Properties dialog box, reminding you that Date2 must be later than DateField.
| Top of Page |
Last Updated: Saturday, July 7, 2001