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:  eCommerce Inventory Management Software for Microsoft Access Templates


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

  • access table validation rule
  • ms access validation rule examples
  • microsoft access validation rule
  • access validation lookup
  • validation rule access
  • validation rule in access
  • access integer number between 1 and 999 VALIDATION RULE
  • validation rule for inputting a number between 0 and 400
  • access validation rule examples
  • validation rule access examples
Read Also:  How to Write Query in Microsoft Access

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

MS Access VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to

Must read×