Microsoft Access - Prevent Erroneous Data By Applying Validation Rules (Access 97/2000/2002)
The more you control the quality of the data being entered into tables, the better results you'll get when you need to retrieve, analyze, or manipulate the data. To ensure that data conforms to your business needs, you can use data validation rules that prevent unacceptable entries. For example, say you have a table storing purchase information and you want to require that the quantity field always has a number greater than zero, or that a date field only contains values after a specific date. You can easily create validation rules that require correct entries and provide messages to users when information violates the rules.
To incorporate validation rules, set the Validation Rule and Validation Text properties at either the control or table field level. For instance, to require that a value is greater than zero, you can use the following property settings:
- Validation Rule: >0
- Validation Text: Quantity must be greater than 0
Likewise, date field validation might look like:
- Validation Rule: >=#1/1/2001#
- Validation Text: Only dates from 2001 and later are accepted
When a user enters an inappropriate value, the message stored in the Validation Text property is displayed.