Using validation rules in Access for better data integrity

Over the past few days, we’ve talked about how important it is that the data in your database be entered in a consistent manner. We’ve looked at how to use forms to limit the control others have over your data. We’ve talked about setting up field types so that people don’t enter text where the date is supposed to go.

There's an even more powerful tool to ensure data integrity: validation rules. This feature of Access lets you create the conditions that data entered must meet, such as an acceptable range of numbers. If someone tries to enter something that doesn’t meet your conditions, they receive an error message and must try again. This can stop incorrect data from ever touching your database.

You can set validation rules both in the table and on any data entry form. For tables, you can specify rules in the Designer under table properties. On forms, you can add rules to the properties of each control.

Since you can have rules both in your form and in the underlying table, be careful that your rules don’t conflict with each other. For example, if your table field required a date earlier than 01/22/2010, but the form required a date later than 01/22/2010 in that field, the user would get stuck in a trap and be blocked from entering anything.

The validation rules themselves are simply expressions, often with an equal, greater-than, or less-than sign or connectors like “and” and “or.” Once you get the hang of it, it’s pretty easy to write the rules. In the expression, the name of your field goes in [square brackets] and if you use dates, they need to be surrounded by the # sign (for example, #01/22/2010#) .

Here are a few sample validation rule expressions to get you started:

If you want users to enter a number between 1 and 100, your validation rule for that field would be:


See, that’s pretty easy!

If you’re working on an order form and the quantity for a particular item must be at least a dozen, your validation rule would be:


Continuing with our order form example, let’s say you want the promised delivery date to be at least a week from the order date. If your database fields are called OrderDate and DeliveryDate, your validation rule would be this:


Finally, if you want the date field to contain only dates before January 22, 2010, your validation rule would look like this:


Once you understand the system, it’s really pretty easy to write your own validation rules. You can find ideas for other expressions by reviewing this Office Online article.

While it’s important to make sure that your rule does exactly what you want it to, it’s also important to write error messages that are easy for your users to understand. A generic error doesn’t tell them what they did wrong.

In our delivery date example, an appropriate error message would be something like:

“The delivery date must be at least 7 days after the order date.”

Be nice, but be clear. After all, the goal is to ensure everyone entering data is working together to ensure the best possible validity.