Access Table Data Validation Property to validate data

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.

access_data_validation

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)

access_data_validation_02

Switch to DataSheet View of the Table. Now change a salary value to -1, the following error message will pop up.

access_data_validation_03

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.

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *