This Access tutorial explains how to validate data type and data value in Access Table using Data Validation Property.
Access Table Data Validation
When you create a Table, you can control the data type of the Field in Design View of the Table.
Assume that you have created a table of the following Fields.
| Field Name | Data Type |
| Department | Long Text |
| Employee ID | Number |
| Salary | Number |
| Employment Date | Date/Time |
When you type a Text in Salary, you will be prompted an error, and you cannot save the record. You will not be able to save the value even if you are using Query to input the data.
This kind of validation is for Data Type only, so what about the value of the data?
Switch to Design View of the Table.
Select Salary, and change the Validation Rule to >0 (meaning user must input a value in Salary >0)
Switch to DataSheet View of the Table. Now change a salary value to -1, the following error message will pop up.
Instead of showing the above message, you can customize an error message in the Validation Text right under the Validation Rule.
Other examples – Access Table Data Validation
I refer to Microsoft support and find some examples below.
| Validation rule | Description | |
| Number | BETWEEN 0 AND 1 | Enter a value with a percent sign. (For use with a field that stores number values as percentages). |
| Date | >=#01/01/2007# AND <#01/01/2008# | Date must occur in 2007. |
| Date | <Date() | Birth date cannot be in the future. |
| Date | [EndDate]>=[StartDate] | Enter an ending date on or after the start date. |
| Text | M Or F | Enter M for male or F for female. |
| Text | LIKE “[A-Z]*@[A-Z].com” OR “[A-Z]*@[A-Z].net” OR “[A-Z]*@[A-Z].org” | Enter a valid .com, .net, or .org e-mail address. |
Operators for Access Table Data Validation
I refer to Microsoft support and find a list of Operators that can be used in Data Validation.
| Operator | Function | Example |
| NOT | Tests for converse values. Use before any comparison operator except IS NOT NULL. | NOT > 10 (the same as <=10). |
| IN | Tests for values equal to existing members in a list. Comparison value must be a comma-separated list enclosed in parentheses. | IN (” Tokyo”,”Paris”,”Moscow “) |
| BETWEEN | Tests for a range of values. You must use two comparison values — low and high — and you must separate those values with the AND separator. | BETWEEN 100 AND 1000 (the same as >=100 AND <=1000) |
| LIKE | Matches pattern strings in Text and Memo fields. | LIKE “Geo*” |
| IS NOT NULL | Forces users to enter values in the field. This is the same as setting the Required field property to Yes. However, when you enable the Required property and a user fails to enter a value, Access displays a somewhat unfriendly error message. Typically, your database is easier to use if you use IS NOT NULL and enter a friendly message in the Validation Text property. | IS NOT NULL |
| AND | Specifies that all the data that you enter must be true or fall within limits that you specify. | >= #01/01/2007# AND <=#03/06/2008# |
| Note You can also use AND to combine validation rules. For example: NOT “UK” AND LIKE “U*”. | ||
| OR | Specifies that one or more pieces of data can be true. | January OR February |
| < | Less than. | |
| <= | Less than or equal to. | |
| > | Greater than. | |
| >= | Greater than or equal to. | |
| = | Equal to. | |
| <> | Not equal to. |
