Validate or restrict data entry in tables

A field validation rule is used to check the value entered into a field as the user leaves the field. A record validation rule controls when an entire record can be saved. Unlike field validation rules, record validation rules can refer to other fields.

You can also determine whether data that already existed before you specified a validation rule or changed the Required or AllowZeroLength property violates the current settings.

1. Open a table in Design view.
2. Do one of the following:

   Define a validation rule to control what values can be entered into a field

   1. In the upper portion of the window, click the field you want to define a validation rule for.
   2. In the lower portion of the window, click the ValidationRule property box, and then type the validation rule, or click the Build button to create the validation rule using the Expression Builder.

      For example, you could define the validation expression ">9" for a Quantity field to prevent a user from placing an order for fewer than 10 units.

   3. In the ValidationText property box, type the message that you want displayed when the rule is broken.

      For example, for the validation expression ">9", you could enter "You must order 10 or more units."

   4. If you set a validation rule in a field that contains data, Microsoft Access will ask if you want to apply the new rule to existing data when you save the table. If you click Yes, Access will warn you when existing data violates the validation rule.

   Define a validation rule to control when a record can be saved

   1. Click Properties on the toolbar to display the table's property sheet.
   2. In the ValidationRule property box, type the validation rule. Or click the Build button to create the validation rule using the Expression Builder.

      For example, you could define the validation expression "[RequiredDate]<=[OrderDate]+30" to make sure that the date entered into the RequiredDate field is within 30 days of the date in the OrderDate field.

   3. In the ValidationText property box, type the message that you want Microsoft Access to display when the rule is broken.

      For example, for the validation expression "[RequiredDate]<=[OrderDate]+30", you could enter "The required date must be within 30 days of the order date."

   4. If you set a validation rule in a table that contains data, Microsoft Access will ask if you want to apply the new rule to existing data when you save the table. If you click Yes, Microsoft Access will warn you when existing data violates the validation rule.

   Test data that already existed before you specified a validation rule

   • Right click the title bar of the table, and click Test Validation Rules.

     Microsoft Access will warn you if existing data violates a validation rule or the Required or AllowZeroLength settings.