Contents

Access validation rule tutorial and examples

One very important job in database design is keeping your data clean and well organized. A database professional would say it is important to maintain the integrity of your data. Input validation rules are an important tool that help you maintain this integrity.

Input validation rules are rules that restrict what values can be entered into a database field. You could, for example create an input validation rule that makes sure a number falls between 1 and 1000. Or you could create an input validation rule that checks if a valid e-mail address was entered.  This tutorial will show you how to create your own input validation rules in Access. I will show you validation rule examples for validatinn text, numbers and dates.

Creating a basic Text validation rule

In the following steps I'll show you how to create a simple input validation rule that checks if the account number is 8 characters long and contains only numbers.

bank account table

The Bank Account table above can be found in this Bank Database download. If you want to follow the exact stepts I took, download the database and open it in Access 2010.

You can follow this validation rule tutorial on video or you can follow the steps below the video.

 

  1. Open the Bank Database download in Access 2010, or create a table with a similar Text field in your own database. (Yes, the AccountNumber field is actually of type Text. This is because we are not going to use the field as a number. We are not going to add or substract account numbers).
  2. Open the table in Design View and select the AccountNumber field, or your own Text field.
  3. At the bottom of the screen find the "Validation rule" field.

    Validation rule
  4. In the Validation Rulefield type one of the following validation rules.

    Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

    This Text validation rule uses character classes to validate the input. It literally says "allow any value that consists of 8 number values". The [0-9] part means "a character between and including 0 and 9".

    Alternatively, you could use the following validation rule.

    Like "########"

    This validation rule achieves the same result, but it uses the number sign (#), which allows only numbers. On the next page you will see more examples that use the number sign.

  5. In the Validation Text field type an error message like. This message is displayed if the value entered doesn't conform to the Validation Rule.

    Account number must consist of 8 numbers

  6. Now, save your table and try to insert a new record in your table. If you try to enter a new record with a AccountNumber that consists of only 6 characters, Access will not insert the record. Instead it will now complain!

    Validation rule tested

So far for this validation rule example. On the next pages of this tutorial we will look at validation rules more closely. You will learn what validation rules can be applied to text, dates and numbers and I will conclude this tutorial with more examples.