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.

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.

 

 

Access Table Data Validation Property to validate data