Summary:
This post is completely dedicated to giving you an overview of MS Access validation rule. Through this blog, you can also learn how to use field validation rules and much more…!
Understanding MS Access Validation Rule:
The validation rule of Access is the best option to prevent invalid data entry errors. MS Access data validation rule makes a complete check over your inserted data just by applying a certain set of criteria.
If any user enters wrong data that doesn’t follow the applied criteria. Then this will instantly throw an error message and reject all to accept any of your data.
Carrying out such a task is quite complex especially when if anyone looking to use it for a bunch of text variables.
YOU MAY ALSO LIKE: Fix Access Database Not Updating Issue
Why Do You Need To Use MS Access Validation Rule?
MS Access validation rule works in controlling and restricting user data input in the table field. Well, you can take it as a set of rule guidelines that keeps track of all your entered data. So, that each field will be assigned with correct data.
MS Access allows so many options like field sizes, table properties, data types, input masks for data validation. Among all these, table properties are the only one that is very particular and matches exactly with the data validation type.
The first way is by choosing the YES option for the required property. Mainly that one that forces to enter values only in some particular field.
The second way is by making use of Validation Rule property that requires specific values & Validation Text property. This gives easy error warnings.
e.g.: if you are inserting validation rules like >60And <100 within the Access Validation Rule property. Then it will force the user to assign values between 40 to 100.
Overall, the MS Access Validation Text property works great to make an easy check for the area where the mistakes have happened and a solution to remove it.
e.g. validation text to display error message can be “ assign values in between 60 & 100”.
Data Validation Examples:
Validation Rule Description
<100——- Must be less than 100.
<=100——– Must be less than or equal to 100.
Between 1 and 10———- Must be between 1 and 10.
0———– Must not equal 0.
<1/1/95———–Must be a date before 1/1/95.
>= Date( )——–Must be today’s date or later.
<= Date( )——– Must be today’s date or earlier.
“Business” Or “Pleasure” Or “Other”——–Must be “Business” or “Pleasure” or “Other.”
Like “??”———–Must have two characters.
Like “####”———- Must have four numbers.
Types Of Validation Rules In Access
Mainly it is of two types:
Field validation rules:
It is used to check the entered value of the field and obstructs you from leaving any of the fields blank until and unless the problem is been resolved.
Eg: if you are using the date field and so assigning the following criteria >=#01/01/2010# in the specific field’s Validation Rule property.
So, you have to put dates either after or on January 1, 2010.
Record validation rules:
This type of Record validation rules is mainly where the user wants to check over the data present in the one field with some other values.
e.g: if in case any user wants product shipping within 30 days of the time period.
If anyone doesn’t ship in that duration then he/she needs to refund some part of the product’s price to the customer.
So, in that situation you will apply the record validation rule in the following manner: [RequiredDate]<=[OrderDate]+30. This will help you to ensure that no one can enter shipping which is too delayed in the future.
Further Reading: Manage User Permissions In Access Database
How To Use Validation Rules In Access?
Let’s learn how to use Access validation rules such as creating a field validation rule and generating a message in case of an invalid data type entry.
How To Use Field Validation Rules?
Create A Field Validation Rule
At first, make a selection of the field that you require to validate.
After then tap to the Fields option present on the ribbon. Now go to the group name “Field Validation”.
In this Field Validation group box tap on to the arrow key at the side of Validation option. Now tap on the Field Validation Rule option.
For the creation of criteria just type the set of rules in the opened Expression Builder area.
To get an easy idea from this article to know how to use Expression Builder in Access.
Steps To Generate Message Alert at the time of Invalid Data Input
1. Choose the field for which you want to display the error message “invalid input of data”. But before performing this make sure that the fields must have a previous validation rule.
2. Now go to the Fields tab, now choose the Field Validation group. From this group, you have hit the Validation option drop-down keys. Then hit on the option ”Field Validation Message”.
3. Give the most suitable message that you want to show. Suppose, you have applied a validation rule such as “>10”. Then in that case message declaration will be like “Enter value within 10.”
Make use of Validation Rules In Access to restrict invalid data input in the Access database. Hopefully, you will use this feature of MS Access in your database.
