MS-Access / Getting Started

Defining a Table Validation Rule

The last detail to define is any validation rules that you want Access 2010 to apply to any fields in the table. Although field validation rules get checked as you enter each new value, Access checks a table validation rule only when you save or add a row. Table validation rules are handy when the values in one field depend on what's stored in another field. You need to wait until the entire row is about to be saved before checking one field against another.

One of the tables in the Contact Tracking database-Products-needs a table validation rule. Define that table now using the specifications in Table-7. Be sure to define ProductID as the primary key, and then save the table and name it Products.

Table-7 Field Definitions for the Products Table
Field NameData TypeDescriptionField Size
ProductIDAutoNumberUnique product identifier
ProductNameTextProduct description100
CategoryDescriptionTextDescription of the category50
UnitPriceCurrncyPrice
TrialVersionYes/NoIn this a trial version?
TrialExpireNumberIf trial version, number of days before expiration

To define a table validation rule, be sure that the table is in Design view, and then click the Property Sheet button in the Show/Hide group of the Design contextual tab on the ribbon.

On the Validation Rule line in the table's property sheet, you can enter any valid comparison expression, or you can use one of the built-in functions to test your table's field values. In the Products table, we want to be sure that any trial version of the software expires in 30, 60, or 90 days. Zero is also a valid value if this particular product isn't a trial version.

To refer to a field name, enclose the name in brackets ([ ]). You'll use this technique whenever you refer to the name of an object anywhere in an expression. In this case, we're using a special built-in function called Immediate If (or IIF for short) in the table validation rule to perform the test on the TrialExpire and TrialVersion fields.

The IIF function can evaluate a test in the first argument and then return the evaluation of the second argument if the first argument is true or the evaluation of the third argument if the first argument is false. Note that we said evaluation of the argument-this means we can enter additional tests, even another IIF, in the second and third arguments.

In the Products table, you want to make sure that the TrialVersion and TrialExpire fields are in sync with each other. If this is not a trial version, the TrialExpire field value should be zero (indicating the product never expires), and if it is a trial version, TrialExpire must be set to some value greater than or equal to 30. The expression we used to accomplish this is as follows:

IIf([TrialVersion]=True,[TrialExpire]>=30,[TrialExpire]=0)

Therefore, the first argument uses IIF to evaluate the expression [TrialVersion] = True-is the value in the field named TrialVersion True? If this is true (this is a trial version that must have a nonzero number of expiration days), IIF returns the evaluation of the second argument. If this is not a trial version, IIF evaluates the third argument. Now all we need to do is type the appropriate test based on the true or false result on TrialVersion. If this is a trial version, the TrialExpire field must be 30 or greater (we'll let the field validation rule make sure it's exactly 30, 60, or 90), so we need to test for that by entering [TrialExpire] >= 30 in the second argument. If this is not a trial version, we need to make sure TrialExpire is zero by entering [TrialExpire] = 0 in the third argument. Got it? If TrialVersion is True, then [TrialExpire] >= 30 must be true or the validation rule will fail. If TrialVersion is False, then [TrialExpire] = 0 must be true. As you might imagine, once you become more familiar with building expressions and with the available built-in functions, you can create very sophisticated table validation rules.

On the Validation Text line of the table's property sheet, enter the text that you want Access to display whenever the table validation rule is violated. You should be careful to word this message so that the user clearly understands what is wrong. If you enter a table validation rule and fail to specify validation text, Access displays the following message when the user enters invalid data: "One or more values are prohibited by the validation rule '< your validation rule expression here >' set for '<table name>'. Enter a value that the expression for this field can accept."

And you can imagine what the user will say about your IIF expression!

[Previous] [Contents] [Next]

In this tutorial:

  1. Designing Client Tables
  2. Creating a New Database
  3. Creating a New Empty Database
  4. Creating Your First Simple Table by Entering Data
  5. Creating a Table Using Application Parts
  6. Creating a Table Using Data Type Parts
  7. Creating a Table in Design View
  8. Understanding Field Data Types
  9. Setting Field Properties
  10. Nulls and Zero-Length Strings
  11. Defining Simple Field Validation Rules
  12. Defining Input Masks
  13. Defining a Primary Key
  14. Defining a Table Validation Rule
  15. Understanding Other Table Properties
  16. Defining Relationships
  17. Defining Your First Relationship
  18. Creating a Relationship on Multiple Fields
  19. Adding Indexes
  20. Multiple-Field Indexes
  21. Setting Table Design Options
  22. Creating a Default Template for New Databases
  23. Printing a Table Definition
  24. Database Limitations