Sat. Feb 8th, 2025
MS Access Validation Rule Lookup Table Examples
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:  Ms Access Outer Join SQL Function

 

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.

Read Also:  Microsoft Access VBA Open Form to a New or Specific Record
Ms Access Validation Rule-4
Ms Access Validation Rule-4

Related Access Database

  • ms access validation rule examples
  • microsoft access validation rule