Field validation rules can help make sure that data you enter into Access tables has correct form and/or meets certain requirements. Validation rules prevent bad data from being entered by restricting user input.
A couple of key things need to be considered when adding field validation rules. First, think about adding “OR IS NULL” to your rule. With this addition, you will have the added benefit of still being allowed to leave the field blank. Otherwise, you may find that you cannot leave the field empty once the validation rule is setup. Another key tip is to consider thoughtfully about whether your validation rule covers 100% of the cases that can be dreamed up. If your validation rule falls short of handling all cases, then do not add that particular validation rule as it cannot be bypassed once in operation. A better alternative route would be to add an event to your form that gives a heads-up warning that some criteria may not be met for a particular field.
How to enter a Validation Rule:
The easiest place to set them is in table design view. Enter the rule using the Validation Rule box found in the lower half. Validation rules can also be added into the Property Sheet when in datasheet view.
Example Validation Rules for Fields:
This table provides some useful example of field validation rules. See this post for more examples.
| Validation Rule for Field | Restriction Set |
| Is Null OR >=0 | Must be a positive number or zero |
| Is Null OR Between 0 and 1 | Values greater than 100% (100% is 1) not permitted |
| Is Null OR Between 10000 and 99999 | Exactly 5 digits (numbers input) required |
| Is Null OR Like “#####” | Exactly 5 digits (text input) required |
| Is Null OR “P” or “F” | Must choose between P (part-time) or F (full-time) |
| Is Null OR <= Date() | Future dates not permitted |
| Is Null OR Between #2000-03-14# And Date() | Must enter a date between March 3, 2000 and today’s date |
| Is Null OR ((Like “*?@?*.?*”) AND (Not Like “*[ ,;]*”)) | Email address format required (Must be at least one character, @, at least one character, dot, at least one character. Space, comma, and semicolon are not permitted.) |
| Is Null OR Like “SKU[0-2]####[ABC] | Specific product code format required (Must start with SKU followed by five-digit numbers and then the letter A, B, or C. The first digit must be 0, 1, or 2.) |