VALIDATION RULES
A validation rule is one way to restrict input in a table field or a control
(such as a text box) on a form. Validation text lets you provide a message to
help users who input data that is not valid.
When data is entered, Access checks to see whether the input breaks a
validation rule – if so, the input is not accepted, and Access displays a
message.
Access provides a number of ways to restrict input:
Data types Every table field has a data type that restricts what users can
enter. For example, a Date/Time field accepts only dates and times, a
Currency field accepts only monetary data, and so on.
Field properties Some field properties restrict data input. For example,
the Field Size property of a field restricts input by limiting the amount of
data.
You can also use the Validation Rule property to require specific values,
and the Validation Text property to alert your users to any mistakes. For
example, entering a rule such as >100 And <1000 in the Validation
Rule property forces users to enter values between 100 and 1,000. A rule
such as [EndDate]>=[StartDate] forces users to enter an ending date that
occurs on or after a starting date. Entering text such as "Enter values
between 100 and 1,000" or "Enter an ending date on or after the start date"
in the Validation Text property tells users when they have made a mistake
and how to fix the error.
Input masks You can use an input mask to validate data by forcing users
to enter values in a specific way. For example, an input mask can force users
to enter dates in a European format, such as 2007.04.14.
You can use these methods of validating data alone or in combination with
each other. Data types are not optional, and provide the most basic type of
data validation.
Validation rule Validation text
<>0 Enter a nonzero value.
>=0 Value must be zero or greater.
-or-
You must enter a positive number.
0 or >100 Value must be either 0 or greater than 100.
BETWEEN 0 AND 1 Enter a value with a percent sign. (For use with
a field that stores number values as
percentages).
<#01/01/2007# Enter a date before 2007.
>=#01/01/2007# AND <#01/01/2008# Date must occur in 2007.
<Date() Birth date cannot be in the future.
StrComp(UCase([LastName]), Data in a field named LastName must be
[LastName],0) = 0 uppercase.
>=Int(Now()) Enter today's date.
M Or F Enter M for male or F for female.
LIKE "[A-Z]*@[A-Z].com" OR "[A-Z]*@[A- Enter a valid .com, .net, or .org e-mail address.
Z].net" OR "[A-Z]*@[A-Z].org"
[RequiredDate]<=[OrderDate]+30 Enter a required date that occurs no more
than 30 days after the order date.
[EndDate]>=[StartDate] Enter an ending date on or after the start date.
Operator Function Example
NOT Tests for converse values. Use before any NOT > 10 (the same as <=10).
comparison operator except IS NOT NULL.
IN Tests for values equal to existing members in IN ("Tokyo","Paris","Moscow")
a list. Comparison value must be a comma-
separated list enclosed in parentheses.
BETWEEN Tests for a range of values. You must use two BETWEEN 100 AND 1000 (the same
comparison values — low and high — and as >=100 AND <=1000)
you must separate those values with the
AND separator.
LIKE Matches pattern strings in Text and Memo LIKE "Geo*"
fields.
IS NOT Forces users to enter values in the field. This IS NOT NULL
NULL 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.
AND Specifies that all parts of the validation rule >= #01/01/2007# AND
must be true. <=#03/06/2008#
Note: You can also use AND to combine
validation rules. For example: NOT "UK"
AND LIKE "U*".
OR Specifies that some but not all parts of the January OR February
validation rule must be true.
< Less than.
<= Less than or equal to.
> Greater than.
>= Greater than or equal to.
= Equal to.
<> Not equal to.