Access validation rules for tables

On the previous pages you saw examples of field validation rules. Field validation rules allow access to accept or reject a new record based on the value of an individual field. But sometimes you need a valdation rule that involves multiple fields. This type of validation rule is called a table validation rule.

Creating table validation rules

Follow the steps below to create a table validation rule.

  1. Open the table you want to create a table validation rule for in Design View. (Right click the table name and choose Design View)
  2. In Design View, look at the right side of the screen to find the Property Sheet

    Table validation rule
  3. Type your validation rule in the Validation Rule field of the Property Sheet. The example rule I created is for an Orderstable. It checks if the order date is before the shipping date, because shipping can never occur before something is actually ordered.

    [OrderDate] <= [ShippingDate] 

    This rule includes two fields of the same table, OrderDate and ShippingDate. When you include field names in a validation rule, you must enclose them in square brackets.

  4. Enter an error message in the Validation Text field. Access will show this message when the validation rule is not met.

When we now try to enter a new record into the Orders table with a shipping date that lies before the order date, the error message is shown and the record is not inserted.

Table validation text

More table validation rule examples

Below are more examples of validation rules for tables. Because table validation rules include multiple fields, these rules often include logical operators like And, Or and Xor. You will learn more on logical operators on the next page.

Example Description
[ZipCode] Is Not Null Or [Address] Is Not Null The ZipCode or the Address or both must have a value.
[ZipCode] Is Not Null Xor [Address] Is Not Null Either the ZipCode or the Address must have a value, but not both
[FieldNr1] - [FieldNr2] > [FieldNr3] The value of FieldNr1-FieldNr2 must be greater than the value of FieldNr3
[FieldNr1] Is Not Null And [FieldNr2] Is Not Null Both FieldNr1 and FieldNr2 must have a value

Note that one table validation rule can replace multiple field validation rules, because you can stick multiple rules together with the And logical operator. The last example rule in the table is a rule that actually combines two field validation rules in one table validation rule.