Microsoft Access let its user to validate data entry by using its powerful feature ‘validation rule’ for preventing data entry error message. Besides that, it also facilities users to display error message in case of invalid data entry.
By writing simple and easy expressions in Access validation fields user can organize data entries from various fields & apply validation rules on it.
If you haven’t any idea on how to use Validation Rule in MS Access 2010/2013/2016/2019 then also you need not to worry…! Just go through this post.
As this post will describe you the complete information on MS Access validation rule, it’s example, types etc. Also explore easy ways to create field validation rule, record validation rule, add validation rule to a control on a form, table and much more….!
Understanding MS Access Validation Rule:
MS Access validation rule is one of the best options to prevent data-entry errors. Access data validation rule actually checks the inserting data by applying specified set of rules. If your entered data doesn’t match the criteria applied for that field then it will show error message and rejects your data.
Data validation rule work great for number, time/date fields and currency. But you can make a validation rule for the text entries also. Performing such task can be a complex one mainly if you are willing to test for many text variables.
What’s The Need Of Using MS Access Validation Rule?
Microsoft Access validation rule helps in restricting or controlling user’s input in table field. One can consider it as set of layers that ensures the user’s entered data appropriately.
MS Access gives you the several options such as data types, field sizes, table properties, and input masks for validating the data. Out of these, table properties allow very precise and exact data validation type.
One way is to setting the required property to the YES option that forces the users to put value in that particular field only.
Another way is to make use of Validation Rule property which needs specific values and Validation Text property for getting easy error alerts.
Eg: suppose you are setting rule like this >40 And <100 in Access Validation Rule property. This will force user to insert values from 40 to 100
All in all, MS Access Validation Text property helps user to make an easy check where the mistake occurred and how to fix it. e.g. validation text in this condition must be like this: “insert values between 40 and 100”.
Data Validation Examples
|<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
Basically, there are two types of Access validation rules. let’s discuss about each of them one by one:
Field validation rules:
This specific type of Access validation rules is set to check value entered in field and prevents you to leave field until the problem get fixed.
Eg: Suppose you are using a date field in which you have set >=#01/01/2010# criteria in that specific field’s Validation Rule property. Now you need to enter dates either on or after the January 1, 2010.
Mainly there are two boxes which relate to Field validation rules. These are as follows:
- Validation Rule box:
This option is used to specify requirements for data inserted within the field.
- Validation Text box:
Using this option user can assign the alert message which appears to user. Message will pop-up when data violates the validation rule entered by the user.
Record validation rules:
This record validation rule is used to control data while saving a record.
Unlike above mentioned field validation rule, using Record validation rules user can refers to some other fields present in the exact same table.
Generally, the record validation rules are been created when any user needs to make a check for the values present in one field with the values of another.
Eg: Suppose you need to ship a product under 30 days of time period. If don’t ship it in that time duration, then you need to refund the part of product’s cost price to the customers.
In this case, you will define the record validation rule like this [RequiredDate]<=[OrderDate]+30 to make sure any user doesn’t enter the shipping date too late in the future.
How To Use Validation Rules In Access?
In this section we will learn how to use the above mentioned two types of Access validation rules i.e field validation rules and Record validation rules.
How To Use Field Validation Rules ?
Create A Field Validation Rule
- First of all, choose the field which you needs to validate.
- tapping on the Fields option on the ribbon, you will reach to the group name “Field Validation”. From this group tap to the Validation drop down arrow. After then tap on Field Validation Rule option.
- Make use of the Expression Builder for the creation of rule.
From here you can get complete information about how to use Expression Builder in Access.
Create A Message Alert To Display For Invalid Data Input In The Field
- Make selection of the field which requires a message to be displayed for invalid input of data. The field must already have a previous validation rule.
- now from the Fields tab, get into the Field Validation group, and tap to the Validation option. After then tap to the Field Validation Message option.
- Now enter the most appropriate message you want to display. Suppose if you have assigned a validation rule like this “ >10”. Then the message declaration must be like this “Enter any value under 10.”
How To Use Record Validation Rules ?
Create a record validation rule
- At first open the table in which you need to validate the records.
- now from the Fields tab, get into the Field Validation group, and tap to the Validation option. After then tap to the Record Validation Rule option.
- Tapping to above option will open Expression Builder dialog box for writing the validation rule.
Create a message to display for record input that is not valid
- Open Access database table in which you want to display message alert for entering invalid data. The table must have previopusly made record validation rule; if it’s not then create it first, as shown in the above section.
- tapping on the Fields option on the ribbon, you will reach to the group name “Field Validation”. From this group tap to the Validation drop down arrow. After then tap on Record Validation Message
- Now it’s time to enter the appropriate message. If in case the validation rule is like this [StartDate]<[EndDate] then the message must be like “StartDate must precede EndDate.”
Where You Can Use Validation Rules In Access 2016?
User can define the Access validation rule for controls on forms and for table fields. If you are defining Validation rules for tables, then those rules are applicable at the time of importing data. For adding validation rules in a table, just open the table and make use of the commands on Fields tab.
For adding validation rule to a form, at first open your Access form in Layout view. After then add rules into the properties of each controls.
Below in the next section we will learn how to add validation rule to a table and also into the controls on forms.
How To Add Validation Rule To A Table
Start your respective Access 2010/2013/2016 application. After then open the table on which you want to apply the Access validation rule.
Now you need to choose the field onto which you need to apply field validation. Here in the shown figure we are applying field validation rule on the column Marks.
Tap to the Fields tab present on the menu bar. After then you will see a field validation box within which a Validation drop down is present.
From the drop-down menu choose the validation rule option to write validation expression.
Tapping to that option will open the Expression Builder dialog box. In this you can write expression for validating the field entries.
Here we are considering the condition that all the course carries total of 100 marks but the obtained marks must be lower than 100.
So now we will write a simple expression for validating marks field just like shown in figure.
Within the Expression Element, Values and Categories, there are several expressions present which you can easily apply over the selected field.
Here we are writing the formula,
Tap to OK option, for applying it successfully.
Now we will write a message which will pop-up to clarify the condition that applied above.
For this you just need to go to fields tab and from the validation option tap to the Field Validation Message option.
Now type a message which you want to display, when anyone enters any invalid data. Tap to the OK option to continue.
Your entered validation message will pop-up when any invalid data is entered in table. Thus, this will prevent you from making any data entry error.
How To Add Validation Rule To A Control On A Form
Well you can make use of Validation Text property and Validation Rule property of the form control for adding validation rule to any form or control. This will ultimately help you to validate input data to that specific control and also restricts user to insert invalid data.
Note: creating a form automatically through the table by using the Form commands present on menu bar ribbon will set of the validation rule for the fields present in that particular table.
Well a control may have several validation rule for the table fields and for the control that is bound. It is very helpful if anyone wants to set more restrictive rule as compared to the table. Firstly, you need to apply the form rule and after then table rule is to be set. If access table is much restrictive compared to form then rules which are defined for table field will get the precedence.
If both the rule of form and table are mutually exclusive, then they won’t allow you to enter any data.
Eg: suppose you are applying following rule in to the date field of table:
But after then you apply the same rule on form control which is bounded to date field:
Now the date field needs values before year 2010 but here your form control needs dates that have year or after year 2010. That’s the reason why it restricts you to enter any invalid data.
Validation rules are quiet useful feature of Access as it restricts the entrances of invalid data in your tables. Though it’s not that possible always to block things which are invalid.
Make use of this catchy MS Access feature to add validation rule in your Access database and prevent yourself from data entry error. If at any stage you encounter any issue to apply Validation rules in Access 2016. Then share your problem with us in our FAQ section. We will immediately help you to provide solution to fix it.