Field Validation Rules for Blocking Bad Data - Applying a Field Validation Rule (Page 2 of 4 ) Each field can have a single validation rule. The following set of steps show you how to set one up. You’ll start out easy, with a validation rule that prevents a numeric field from accepting 0 or any negative number (and in the following sections you’ll hone your rule-writing abilities so you can tackle other data types). Here’s how to add your validation rule: In Design view, select the field to which you want to apply the rule.
All data types—except Memo, AutoNumber, and OLE Object—support validation. The validation rule in this example works with any numeric data type (like Number or Currency).
In the Validation Rule field property, type a validation expression (Figure 4-14).
An expression’s a bit of SQL that performs a check on the data you’ve entered. Access performs its validation check when you finish entering a piece of data, and try to navigate to another field or another record. For example, >0 is a validation rule that forces the value in a Number field to be larger than 0. You’ll learn more validation rules in the following sections.  Figure 4-14.Here, the Validation Rule property prevents
impossible prices, and the Validation Text
provides an error message. Type some error-message text in the Validation Text field property.
If you enter a value that fails the validation check, then Access rejects the value and displays this error text in a dialog box. If you don’t supply any text, then Access shows the validation rule for the field (whatever you entered in step 2), which is more than a little confusing for most mere mortals.
Right-click the tab title, and then choose Datasheet View.
If your table has existing records, Access gives you the option of checking them to make sure they meet the requirements of your validation rule. You decide whether you want to perform this check, or skip it altogether. Once you’re in Datasheet view, you’re ready to try out your validation rule (Figure 4-15).  Figure 4-15.Here, a validation rule of >0 prevents negative
numbers in the Price field. When you enter a
negative number, Access pops up a message box
with the validation text you defined, as shown
here. Once you click OK, you return to your field,
which remains in edit mode. You can change
the value to a positive number, or press Esc to
cancel the record edit or insertion.
Note: Just because your table has validation rules doesn’t mean the data inside follows these rules. A discrepancy can occur if you added records before the validation rules came into effect. (You learned about the same potential problem with required fields on page 116.) To avoid these headaches, set up your validation rules before you start adding data.
Next: Writing a Field Validation Rule >>
More MS SQL Server Articles
More By O'Reilly Media |  This article is excerpted from chapter four of the book Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Check it out today at your favorite bookstore. Buy this book now.
|
|
|
