Field Validation Rules for Blocking Bad Data
(Page 1 of 4 )
Yesterday we discussed using masks to help with data validation issues. This time we'll be finishing our discussion of masks and talk about applying field validation rules. This article is excerpted from chapter four of the book
Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
Validation Rules
POWER USERS' CLINIC
Adding Your Mask to the Mask List
Sometimes you may create a mask that’s so useful you want to use it in many different tables in your database (and maybe even in different databases). While you can certainly copy your mask to every field that needs to use it,
Access has a nicer option—you can store your mask in its mask list. That way, the mask shows up whenever you run the Input Mask wizard, right alongside all Access’s other standard masks.
To add your mask to the list, head to the Input Mask field property (for any field), and then click the ellipsis button to fire up the Input Mask wizard. Then, click the Edit List button, which pops up a handy window where you can edit the masks that Access provides, and add your own (Figure 4-13).
Figure 4-13.
To add your own mask, use the record scrolling buttons (at the bottom of this window) to scroll to the end. Or you can use this window to change a mask. For example, the prebuilt telephone mask doesn’t require an area code. If that’s a liberty you’re not willing to take, then replace it with the more restrictive version (000) 000-0000.
Input masks are a great tool, but they apply to only a few specific types of information—usually fixed-length text that has a single, unchanging pattern. To create a truly bulletproof table, you need to use more sophisticated restrictions, like making sure a number falls in a certain range, checking that a date hasn’t yet occurred, or verifying that a text value starts with a certain letter. Validation rules can help you create all these restrictions by drawing on the full power of the SQL language.
Note: You’ll get a more thorough introduction to SQL starting in Chapter 6. Fortunately, you need only a dash of SQL to write a validation rule. The key ingredient’s a validation expression, and you’ll see several practical examples of expressions that you can drop straight into your tables.
A validation rule’s premise is simple. You set up a restriction that tells Access which values to allow in a field and which ones are no good. Whenever someone adds a new record or edits a record, Access makes sure the data lives up to your validation rules. If it doesn’t, then Access presents an error message and forces you to edit the offending data and try again.
Next: Applying 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.
|
|
