Use Access validation rules to ensure accurate data entry

Your Access database is only as sound as the data it stores. Data types can only do so much. The next step is to constrain input values using validation rules.

Protecting the validity of your data is one of the most important tasks of a database developer. To ensure that users enter accurate data, start at the foundation. By using validation rules, you can ensure accurate data entry (to a point).

Validation rules catch errors at the data entry level, so in effect, the mistakes never make it into your database. If the data doesn't satisfy the conditions you've set, Access won't accept the input value. You don't have to catch anything later and correct it, so validation rules are more efficient than cleaning up after the fact.

You can apply a validation rule to a field or to a record. At the field level, the rule checks the data for only the field constrained by the rule. For example, you might restrict an hours worked field to values between 0 and 40 (inclusive). To do so, you'd use the following expression on an Integer field: <=40 And >=0. When a user enters a value other than 0 through 40, Access displays an error message and rejects the input value.

Record-level rules compare data from two or more fields. When the rule determines a conflict, Access rejects the record,  not just a single input value. For instance, your hours table might include an overtime field. Only when the hours worked value equals 40, do you want the overtime field to accept an Integer greater than 0. In this case, you'd use a record-level validation rule, which is actually a table property that you can set as follows:

  1. Right-click the table (in Design view) and select Properties.
  2. In the Validation Rule property, enter the expression ([overtime]=0) Or ([hours]=40).
  3. Save the table. This rule will accept a value other than 0 in the Overtime field only when the Hours value equals 40.

To protect the validity of your data, you start at the foundation. The field's data type will restrict input values to specify types of data. The second line of action is the field and table validation rule, which restricts the input value conditionally. (Use the Validation Text property to display a custom message when the data doesn't satisfy the validation rule.)

Keep in mind that validation rules can't prevent all input errors. For instance, an employee who works 23 hours might transpose the digits and enter 32--there's no rule that's going to catch that error.