About validating or restricting data entry in tables

Microsoft Access provides a variety of ways to control how data is entered in fields in your table.

The first thing you should consider is whether a field has an appropriate data type. For example, Date/Time fields only allow entry of valid date and time formats. If you are currently using a Text field for such information, it may be appropriate to change the field to a Date/Time field.

There are also additional properties that you can set for a field to provide further control over the values that can be entered into the field:

• For Number fields, you can choose from a variety of field sizes to control the kind and range of values that can be entered.
  
  
• For Text fields, you can set the maximum number of characters that can be entered.
  
  
• For all but AutoNumber fields (which generate their own data), you can require that data be entered in the field.
  
  
• For Text, Date/Time, and Number fields, you can define an input mask to provide blanks to fill in and can control what values can be entered in those blanks.
  
  
• For all but OLE Object fields, you can prevent duplicate values from being entered into a field or combination of fields.

You can also define two kinds of validation rules: field validation rules and record validation rules. Validation rules allow you to define a rule to limit what will be accepted.

• A field validation rule is used to check the value entered into a field as the user leaves the field. For example, you could define ">=10 And <=100" as the validation rule for a Number field to allow only values from 10 to 100 to be entered.
  
  
• A record validation rule controls when an entire record can be saved. Unlike field validation rules, record validation rules can refer to other fields in the same table. This makes them useful when you want to compare values in different fields. For example, you could define "[RequiredDate]<=[OrderDate]+30" as the validation rule for an Orders table. This rule would make sure that the date entered into the RequiredDate field is within 30 days of the date in the OrderDate field.

Both kinds of validation rules will display a message that you define when the rule is broken to inform the user how to properly enter data.

Return to Validate or restrict data entry in tables