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.
- Open the table you want to create a table validation rule for in Design View. (Right click the table name and choose Design View)
- In Design View, look at the right side of the screen to find
the Property Sheet.
- 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.
- 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.
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.
|[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.