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