Creating a Table Validation Rule

Field validation rules always apply to a single field. However, database designers often need a way to compare the values in different fields. Suppose you have an Orders table that logs purchases from your monogrammed sock store. In your Orders table, you use two date fields: DateOrdered and DateShipped. To keep everything kosher, you need a validation rule that makes sure DateOrdered falls before DateShipped. After all, how can you ship a product out before someone orders it?

Because this validation rule involves two fields, the only way to put it in place is to create a validation rule for the whole table. Table validation rules can use all the SQL tricks you’ve learned about so far, and they can pull the values out of any field in the current record.

Here’s how to create a table validation rule:

1. In Design view, choose Table Tools | Design -> Show/Hide -> Property Sheet.
   
   A box with extra settings appears on the right side of the window (Figure 4-16).
   
   

   ---

   Note: You can create only a single validation rule for a table. This limit might sound like a problem, but you can get around it by using the And keyword (page 137) to yoke together as many conditions as you want. The validation rule may be a little difficult to read, but it still works without a hitch. 

   ---

   
   
   Figure 4-16.
   The Property Sheet shows some information about the entire table, including the sorting (page 94) and filtering settings (page 97) you’ve applied to the datasheet, and the table validation rule. Here, the validation rule prevents orders from being shipped before they’re ordered.
   
2. In the Property Sheet tab, set the Validation Rule.
   
   A table validation rule can use all the same keywords you learned about earlier. However, table validation rules usually compare two or more fields. The validation rule [DateOrdered]
   < [DateShipped] ensures that the value for the DateOrdered field is older than that used for the DateShipped.
   
   When referring to a field in a table validation rule, you need to include square brackets around your field names. That way, Access can tell the difference between fields and functions (like the Date( ) function you learned about on page 119). 
   
3. Set the Validation Text.
   
   This message is the error message that’s shown if the validation fails. It works the same as the validation text for a field rule.
   
   Lookups
   
   When you insert a new record, Access checks the field validation rules first. If your data passes the test (and has the right data types), then Access checks the table validation rule.

Tip: Once you set the table validation rule, you might want to close the Property Sheet to get more room in your design window. To do so, choose Table Tools | Design -> Show/Hide ->Property Sheet.

Next: Lookups >>
 

More MS SQL Server Articles
More By O'Reilly Media