What are the three 3 types of validation rules in Access?

Microsoft Access lets its user 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 messages 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 how to use Validation Rule in MS Access 2010/2013/2016/2019 then also you need not worry…! Just go through this post.

This post will describe the complete information on MS Access validation rule, it’s an example, types, etc. Also explore easy ways to create field validation rule, record validation rule, add a validation rule to a control on a form, table, and much more….!

Fix Microsoft Access Issues
Run a database Scan with Stellar Access Database Repair Tool to repair inconsistent Access database. After the scan is complete, the repair process will restore the database to its normal state fixing all the errors.

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 a specified set of rules. If your entered data doesn’t match the criteria applied for that field then it will show an error message and rejects your data.

Data validation rule works great for number, time/date fields, and currency. But you can make a validation rule for the text entries also. Performing such a 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 the user’s input in the table field. One can consider it as a set of layers that ensures the user’s entered data appropriately.

MS Access gives you 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 types.

One way is to set 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 the Validation Text property for getting easy error alerts.

Eg: suppose you are setting rules like this >40 And <100 in Access Validation Rule property. This will force the user to insert values from 40 to 100

All in all, MS Access Validation Text property helps the 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

Types Of Validation Rules In Access

Basically, there are two types of Access validation rules. let’s discuss each of them one by one:

This specific type of Access validation rules is set to check the value entered in the field and prevents you to leave field until the problem gets 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 January 1, 2010.

Mainly there are two boxes that relate to Field validation rules. These are as follows:

This option is used to specify requirements for data inserted within the field.

Using this option user can assign the alert message which appears to the user. The message will pop-up when data violates the validation rule entered by the user.

This record validation rule is used to control data while saving a record.

Unlike the above-mentioned field validation rule, using Record validation rules users can refer 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 the time period. If you don’t ship it in that time duration, then you need to refund the part of the 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

1. First of all, choose the field which you need to validate.
2. 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.
3. Make use of the Expression Builder for the creation of rules.

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

1. 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.
2. 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.
3. 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 the above option will open the Expression Builder dialog box for writing the validation rule.

Create a message to display for record input that is not valid

1. Open Access database table in which you want to display message alert for entering invalid data. The table must have previously made record validation rule; if it’s not then create it first, as shown in the above section.
2. 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
3. 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?

Users 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 the Fields tab.

For adding validation rule to a form, at first, open your Access form in the Layout view. After then add rules into the properties of each control.

Below is the next section, we will learn how to add a 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 an expression for validating the field entries.

Here we are considering the condition that all the course carries a total of 100 marks but the obtained marks must be lower than 100.

Syntax

[Field_Name]<Numeric_Value

So now we will write a simple expression for validating marks field just like shown in the figure.

Within the Expression Element, Values, and Categories, there are several expressions present that you can easily apply over the selected field.

Here we are writing the formula,

[Marks]<100

Tap to the 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 the 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 the table. Thus, this will prevent you from making any data entry errors.

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 the user to insert invalid data.

Note: creating a form automatically through the table by using the Form commands present on the menu bar ribbon will set the validation rule for the fields present in that particular table.

Well, a control may have several validation rules for the table fields and for the control that is bound. It is very helpful if anyone wants to set more restrictive rules as compared to the table. Firstly, you need to apply the form rule and after then table rule is to be set. If the access table is much restrictive compared to form then rules which are defined for the 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 into the date field of the table:

<#01/01/2010#

But after then you apply the same rule on form control which is bounded to date field:

>=#01/01/2010#

Now the date field needs values before the year 2010 but here your form control needs dates that have a year or after the year 2010. That’s the reason why it restricts you to enter any invalid data.

Conclusion:

Validation rules are a 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 comment section. We will immediately help you to provide a solution to fix it.

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.

1. Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
2. Click Browse and Search option to locate corrupt Access database.
3. Click Repair button to repair & preview the database objects.