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 Substring 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.

Ms Access Validation Rule-4

Ms Access Validation Rule-4

Related Access Database

  • validation rule examples access
  • access 2010 validation rule examples
  • examples of access database tables
  • validation rule for websites in access
  • access validation rule list
  • validation rules for lookup
  • access validation rule
  • access validation examples
  • access table validaton rule for html addresses
  • Access record validation rule for text in criteria
Read Also:  MS Access IsNull Function with Examples in SQL Query and VBA Code


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
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select


Must read×

Top