Microsoft Access Validation Rules In Tables

Microsoft Access Validation Rules In Tables

A validation rule restricts or controls what a user can input in a table field. You can think of it as a set of layers which ensures that your users input data properly.

Here’s a quick video tutorial to get you going and very easy to apply.

Microsoft Access provides several ways to validate data: data types, field sizes, table properties, and input masks. Among these, table properties provide specific types of data validation.

One example is setting the required property to YES which results to forcing the users to enter a value in that specific field.

Another technique is by using the Validation Rule property to require specific values, and the Validation Text property to alert your users to any errors. For example, putting a rule such as >50 And <100 in the Validation Rule property forces users to input values between 50 and 100. Another example is [EndDate] >= [StartDate], which forces users to enter an ending date that occurs on or after a starting date.

The Validation Text property tells the users if they have made a mistake in entering the data and how to fix the error. An example of a validation text is “Enter values between 50 and 100”.

Microsoft Access Validation Rules In Tables: The Key Steps

To create a Validation Rule, here are the steps:

  1. Open your table in MS Access and go to design view.
  2. Select the field that you want to add that Validation Rule to.
  3. Scroll down and you will see the properties pane for that specific field.
  4. On the Validation Rule section type in the validation rule expression that forces users to type in specific text.

Here are some Validation Rule examples that you can use:

  • Is Null OR Not Like “*[!a-z]*” — accept letters from a to z only; any character outside the range is rejected.
  • Is Null OR Not Like “*[!((a-z) or (0-9))]*” — accept digits and letters only; accepts A to Z and 0 to 9, no other characters.
  • Is Null Or Not Like “*[!a-z OR “” “”]*” — accept letters and spaces only; punctuation and digits are rejected.
  • Is Null OR Like “????????” — accept exactly 8 characters; the question mark stands for one character.
  • Is Null OR >= 0 — accept positive numbers only.
  • Is Null OR <= Date() — not a future date.
  1. Save the changes.

Validation rules are very useful for restricting bad data in your tables. However, you must not go overboard and block things that might be valid, although unexpected.

Microsoft Access Validation Rules In Tables

To learn more about good design techniques, my eBook bundle may just be the ticket offering a 30 day email support backed with a money back guarantee – what could be better?

Leave a Reply

Your email address will not be published. Required fields are marked *