In this article I have explain how to add validation rules in an Access table. Using validation rules, you can check what users can enter in a given field. Validation rules prevent bad data being saved in your table. Implementing validation rules in Access, steps are given below.
Step 1: First create a table in your access database as below image for demonstration.
Figure 1:
Step 2: Apply validation rule on “Name” column as below image. Here I have validated “Name” column that accept only “a-z or A-Z” character only.
Validation Rule: - Is Null OR Not Like "*[!a-z]*"
Validation Text: - Accept letters (a - z) only
Figure 2:
Step 3: Now when you try to enter wrong value into “Name” column that will be display alert message as below image.
Figure 3:
Step 4: Below I have given some important validation which frequently used
S. No. | Validation Rules | Description |
1 | Is Null OR Not Like "*[!a-z]*" | Accept letters (a - z) only |
2 | Is Null OR Not Like "*[!0-9]*" | Accept digits (0 - 9) only |
3 | Is Null OR Not Like "*[!((a-z) or (0-9))]*" | Digits and letters only |
4 | Is Null OR Like "????????" | Exactly 8 characters |
5 | Is Null OR Between 1000 And 9999 | Exactly 4 digits |
6 | Is Null OR Like "####" | Exactly 4 digits |
7 | Is Null OR >= 0 Remove | Positive numbers only |
8 | Is Null OR Between -1 And 1 | No more than 100% |
9 | Is Null OR <= Date() | Not a future date |
10 | Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*")) | Email address |
11 | Not Null | You must fill in Field1 |
12 | Is Null OR "M" Or "F" | Limit to specific choices as Male/Female |
13 | Is Null OR IN (1, 2, 4, 8) | Limit to specific choices |
14 | Is Null OR 0 or -1 | Yes/No/Null field |
Leave Comment