MS Access Validation Rule Lookup Table Examples

The Validation Rule in Microsoft Access is designed to restrict the data entry, so it will prevent the data that do not meet specific criteria to be entered into the database. The Validation Rule can be found in tables object. In the Table, go to Design View. Look at the Field Properties > General tab. The Validation Rule is related with the Data Type. Data Type limits what type of data to be entered (E.g. Date, number only, text only). Validation Rule will give even more specific criteria for the entered value (E.g. Number must be below 50). Make sure that the Validation Rule is following with selected Data Type.

Ms Access Validation Rule-1

Ms Access Validation Rule-1

The Validation List can be seen in this tables:

Text Data Type:

Validation Rule Explanation
“*[!a-z]*” The value only A – Z letter only (Case insensitive).
“*[!0-9]*” The value only from number 0 – 9
“[0-2][0-2][0-2]” The value is in 3 digits. But each digit has the value from 0 to 2.
Is Null The field can be left blank.
Is Not Null The field cannot left blank.
“####” Limit the value to have 4 digits number.
“????” Limit the value to have 4 characters length.
“M” Or “F” The value of the field must be M or F.
“AB*” The value must be started with AB
Read Also:  Access Database Multiple Users Split and Share


Note: In Text Data type, input the expression before the validation rules for better result.

Example:    LIKE “[0-2][0-2][0-2]”

NOT LIKE “*[!a-z]*”


Number and Date:

Validation Rule Explanation
> 0 The number must be greater than 0.
<= Date() The Date must be today or lesser.
>= 0 And <= 100 Between 0 to 100
Between #1/1/2000# And #31/12/2000# The Date must be between 1 January 2000 to 31 December 2000


Note: The lists above are just a few samples. There still lots of validation rules that can be applied.

You can combine 2 or more validation rules by using operators: AND, OR, IN, and still many more.


Example of the Validation Rule:

In the table above, the BookID can be entered in random number length, as long the data is number. Let’s change the BookID into 6-digit length.

Input this syntax into Validation Rule:

Ms Access Validation Rule-2

Ms Access Validation Rule-2

Like “######”

Now, save and run the table. Try to input the BookID with less or more than 6 digits. You will get warning like this:

Ms Access Validation Rule-3

Ms Access Validation Rule-3


For people that not familiar with Access, they will be confused with warning message. This is because the Validation Text row is blank. You need to input the proper text to inform the users.

Ms Access Validation Rule-4

Ms Access Validation Rule-4

Related Access Database

Read Also:  MS Access Inner Join on Multiple Fields and Conditions

Access Database Tags: #how to set validation rule in ms access #microsoft access validation rule list #ms access validation rule #ms access validation rule examples #ms access validation rule if statement #ms access validation rule lookup table #what is validation rule in ms access

Powershell Query MS Access Database in Microsoft Windows Application
Powershell Query MS Access Database in Microsoft Windows Application
Powershell Access Database – Ms. Powershell is
Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function
Ms Access Weekday Function is purposed to

Must read×