Home > DeveloperSection > Articles > Validation rules in Access

Validation rules in Access

MS Access MS Access 
0 Comment(s)
 2474  View(s)
Rate this:

Validation rules in Access

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:

Validation rules in Access

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:

Validation rules in Access

Step 3: Now when you try to enter wrong value into “Name” column that will be display alert message as below image.

Figure 3:

Validation rules in Access

Step 4: Below I have given some important validation which frequently used

S. No.

Validation Rules



Is Null OR Not Like "*[!a-z]*"

Accept letters (a - z) only


Is Null OR Not Like "*[!0-9]*"

Accept digits (0 - 9) only


Is Null OR Not Like "*[!((a-z) or (0-9))]*"

Digits and letters only


Is Null OR Like "????????"

Exactly 8 characters


Is Null OR Between 1000 And 9999

Exactly 4 digits


Is Null OR Like "####"

Exactly 4 digits


Is Null OR >= 0            Remove

Positive numbers only


Is Null OR Between -1 And 1

No more than 100%


Is Null OR <= Date()

Not a future date


Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))

Email address


Not Null

You must fill in Field1


Is Null OR "M" Or "F"

Limit to specific choices as Male/Female


Is Null OR IN (1, 2, 4, 8)

Limit to specific choices


Is Null OR 0 or -1

Yes/No/Null field


Don't want to miss updates? Please click the below button!

Follow MindStick