How to Create Validation Rules in Microsoft Access on Windows 11

Posted on 16th June 2023

Introduction

Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.

You can use validation rules to make sure that the data in a record meets the standards you specify before the record is entered into your database. For example, you can use a validation rule to make sure that a value is entered in a field or that a value is entered as a number greater than 0.

Creating a Validation Rule

Open the table in which you want to create a validation rule.
Click the field in which you want to create the validation rule.
Click the Data Type & Formatting button.
Click the down arrow next to the Validation Rule property, and then type the rule.
If you want to display a message when the rule is violated, type the message in the Validation Text property.
Click OK.

Example

The following validation rule ensures that the value entered in the field is greater than zero:

>>0

The following validation rule ensures that the value entered in the field is a number between 1 and 10:

[1-10]

The following validation rule ensures that the value entered in the field is a number that is not divisible by 3:

MOD(3,0)

You can use the Validation Rule property to specify rules for data entered in a field. For example, you could use a validation rule to ensure that a value is entered in a field, that a value is of the correct data type, or that a value falls within a certain range. You can also use validation rules to ensure that a value is unique. You can set a validation rule for a field in a table, or for a control on a form.

When you set a validation rule for a field in a table, the rule is enforced whenever data is entered in that field in any context. For example, if you set a validation rule for a field in a table, the rule is enforced whenever data is entered in that field in a form or a query.

When you set a validation rule for a control on a form, the rule is enforced only when data is entered in that control on the form. For example, if you set a validation rule for a text box control on a form, the rule is enforced only when data is entered in that text box on the form.

You can set a validation rule by using the Validation Rule property in the following ways:

In the Field Properties section of the Table Design View, click in the Validation Rule property box, and then type the rule.

In the Controls section of the form Design View, click the control to which you want to apply the validation rule, click the Data tab, and then type the rule in the Validation Rule property box.

In the Property Sheet, click the All tab, click in the Validation Rule property box, and then type the rule.

When you set a validation rule, you can optionally set a Validation Text property for the field or control. The Validation Text property allows you to specify a message to display when the validation rule is violated. For example, if you set a validation rule that requires a value to be entered in a field, you can set the Validation Text property to display a message such as “Value required.”

If you set a validation rule for a field in a table, and you do not set the Validation Text property, Access uses the default validation text for the data type of the field. For example, if the field is a text field, the default validation text is “Text is required.”

If you set a validation rule for a control on a form, and you do not set the Validation Text property, the default validation text is “Enter a value.”

You can use the following characters in a validation rule:

(greater than)
= (equal to)
(not equal to)
!= (not equal to)
= (greater than or equal to)

You can use the following operators in a validation rule:

AND
OR
NOT

You can use the following functions in a validation rule:

ISNULL(expression)
IIF(expression, truepart, falsepart)

You can use the following characters as wildcards in a validation rule:

? (any single character)
* (any sequence of characters)

You can use the following characters as delimiters in a validation rule:

” (double quotation mark)
‘ (single quotation mark)
[] (brackets)

You can use the following characters as escape characters in a validation rule:

(backslash)

You can use the following characters as operators in a validation rule:

+ (addition)
– (subtraction)
* (multiplication)
/ (division)
^ (exponentiation)

You can use the following characters as delimiters in a validation rule:

( ) (parentheses)
, (comma)
. (period)
: (colon)
; (semicolon)

You can use the following characters as wildcards in a validation rule:

? (any single character)
* (any sequence of characters)

You can use the following functions in a validation rule:

ABS(number)
ACOS(number)
AND(condition1, [condition2],…)
ASC(string)
ASIN(number)
ATAN(number)
ATAN2(x,y)
AVG(expression)
BETWEEN(expression, start, end)
COUNT(expression)
COS(number)
CREATEOBJECT(progid)
CSTR(expression)
CURR(expression)
DATEADD(interval, number, date)
DATEDIFF(interval, date1, date2)
DATENAME(interval, date)
DATEPART(interval, date)
DAY(date)
EXP(number)
FILTER(array, expression, [keepfiltered])
FIX(number)
FV(rate, nper, pmt, [pv], [type])
HEX(number)
IIF(expression, truepart, falsepart)
INSTR(string1, string2, [start], [compare])
INT(number)
ISDATE(expression)
ISEMPTY(expression)
ISERROR(expression)
ISNULL(expression)
ISNUMERIC(expression)
JOIN(array, delimiter)
LEFT(string, length)
LEN(string)
LN(number)
LOG(number, [base])
LOG10(number)
LTRIM(string)
MAX(expression)
MID(string, start, [length])
MIN(expression)
MIRR(values, finance_rate, reinvest_rate)
NPV(rate, value1, [value2], …)
NPER(rate, pmt, pv, [fv], [type])
NOT(logical)
NOW( )
PV(rate, nper, pmt, [fv], [type])
QLIKTAG(tag_name)
RIGHT(string, length)
RND(number, [decimals])
ROUND(number, decimals)
RTRIM(string)
SIN(number)
SQR(number)
STDEV(expression)
STDEVP(expression)
STR(expression)
SUBSTR(string, start, [length])
SUM(expression)
TAN(number)
TRIM(string)
TYPENAME(expression)
VAL(string)
VAR(expression)
VARP(expression)
WEEKDAY(date, [firstdayofweek])
YEAR(date)

You can use the following characters as wildcards in a validation rule:

? (any single character)
* (any sequence of characters)

You can use the following characters as delimiters in a validation rule:

” (double quotation mark)
‘ (single quotation mark)
[] (brackets)

You can use the following characters as escape characters in a validation rule:

(backslash)

You can use the following characters as operators in a validation rule:

+ (addition)
– (subtraction)
* (multiplication)
/ (division)
^ (exponentiation)

You can use the following characters as delimiters in a validation rule:

( ) (parentheses)
, (comma)
. (period)
: (colon)
; (semicolon)

You can use the following functions in a validation rule:

Previous Post - How to Build an Accordion Plugin for WordPress

Next Post - Creating Custom Shortcodes in WordPress Themes