MS-Access / Getting Started

Defining Simple Field Validation Rules

To define a simple check on the values that you allow in a field, enter an expression in the Validation Rule property box for the field. Access 2010 won't allow you to enter a field value that violates this rule. Access performs this validation for data entered in a Table window in Datasheet view, in an updateable query, or in a form. You can specify a more restrictive validation rule in a form, but you cannot override the rule defined for the field in the table by specifying a completely different rule in the form.

In general, a field validation expression consists of an operator and a comparison value. If you do not include an operator, Access assumes you want an "equals" (=) comparison. You can specify multiple comparisons separated by the Boolean operators OR and AND.

It is good practice to always enclose text string values in quotation marks. If one of your values is a text string containing blanks or special characters, you must enclose the entire string in quotation marks. For example, to limit the valid entries for a City field to the two largest cities in the state of New York, enter "Los Angeles" Or "San Diego". If you are comparing date values, you must enclose the date constants in pound sign (#) characters, as in #07/1/2010#.

You can use the comparison symbols to compare the value in the field to a value or values in your validation rule. Comparison symbols are summarized in Table-4. For example, you might want to ensure that a numeric value is always less than 1000. To do this, enter <1000. You can use one or more pairs of comparisons to ask Access to check that the value falls within certain ranges. For example, if you want to verify that a number is in the range of 50 through 100, enter either >=50 And <=100 or Between 50 And 100. Another way to test for a match in a list of values is to use the IN comparison operator. For example, to test for states surrounding the U.S. capital, enter In ("Virginia", "Maryland"). If all you need to do is ensure that the user enters a value, you can enter the special comparison phrase Is Not Null.

Table-4 Comparison Symbols Used in Validation Rules
OperatorMeaning
NOTUse before any comparison operator except IS NOT NULL to perform the converse test. For example, NOT > 5 is equivalent to <=5.
<Less than
<=Less than or equal to
>Greater than
>=Greater than or equal to
=Equal to
<>Not equal to
INTest for equal to any member in a list; comparison value must be a comma-separated list enclosed in parentheses
BETWEENTest for a range of values; comparison value must be two values (a low and a high value) separated by the AND operator
LIKETest a Text or Memo field to match a pattern string
IS NOT NULLRequires the user to enter a value in the field

When you set the Required property to Yes and the user fails to enter a value, Access 2010 displays an unfriendly message:

"You must enter a value in the '<tablename.fieldname>' field."

We recommend that you use the Validation Rule property to require a value in the field and then use the Validation Text property to generate your own specific message.

If you need to validate a Text, Memo, or Hyperlink field against a matching pattern (for example, a postal code or a phone number), you can use the LIKE comparison operator. You provide a text string as a comparison value that defines which characters are valid in which positions. Access understands a number of wildcard characters, which you can use to define positions that can contain any single character, zero or more characters, or any single number. These characters are shown in Table-5.

Table-5 LIKE Wildcard Characters
CharacterMeaning
?Any single character
*Zero or more characters; use to define leading, trailing, or embedded strings that don't have to match any specific pattern characters
#Any single digit

You can also specify that any particular position in the Text or Memo field can contain only characters from a list that you provide. You can specify a range of characters within a list by entering the low value character, a hyphen, and the high value character, as in [A-Z] or [3-7]. If you want to test a position for any characters except those in a list, start the list with an exclamation point (!). You must enclose all lists in brackets ([ ]). You can see examples of validation rules using LIKE here.

Validation RuleTest For
LIKE "#####" orA U.S. 5-digit ZIP Code
LIKE "#####-####"A U.S. 9-digit ZIP+ Code
LIKE "[A-Z]#[A-Z] #[A-Z]#"A Canadian postal code
LIKE "###-##-####"A U.S. Social Security Number
LIKE "Smith*"A string that begins with Smith1
LIKE "*smith##*"A string that contains smith followed by two numbers, anywhere in the string
LIKE "??00####"An eight-character string that contains any first two characters followed by exactly two zeros and then any four digits
LIKE "[!0-9BMQ]*####"A string that contains any character other than a number or the letter B, M, or Q in the first position and ends with exactly four digits
1 Character string comparisons in Access are case-insensitive. So, smith, SMITH, and Smith are all equal.
[Previous] [Contents] [Next]

In this tutorial:

  1. Designing Client Tables
  2. Creating a New Database
  3. Creating a New Empty Database
  4. Creating Your First Simple Table by Entering Data
  5. Creating a Table Using Application Parts
  6. Creating a Table Using Data Type Parts
  7. Creating a Table in Design View
  8. Understanding Field Data Types
  9. Setting Field Properties
  10. Nulls and Zero-Length Strings
  11. Defining Simple Field Validation Rules
  12. Defining Input Masks
  13. Defining a Primary Key
  14. Defining a Table Validation Rule
  15. Understanding Other Table Properties
  16. Defining Relationships
  17. Defining Your First Relationship
  18. Creating a Relationship on Multiple Fields
  19. Adding Indexes
  20. Multiple-Field Indexes
  21. Setting Table Design Options
  22. Creating a Default Template for New Databases
  23. Printing a Table Definition
  24. Database Limitations