Syntax examples for common validation rule operators
Microsoft Access
Syntax examples for common validation rule operators
|
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 parts of the validation rule must be true. |
>= #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 some but not all parts of the validation rule must be true. |
January OR February |
|
< |
Less than. |
|
|
<= |
Less than or equal to. |
|
|
> |
Greater than. |
|
|
>= |
Greater than or equal to. |
|
|
= |
Equal to. |
|
|
<> |
Not equal to. |
OR
You can also