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.
The Validation List can be seen in this tables:
Text Data Type:
|“*[!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|
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:
|> 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:
Now, save and run the table. Try to input the BookID with less or more than 6 digits. You will get warning like this:
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.
Related Access Database
- validation rule in ms access 2007
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