A lot of work must be put into planning and applying techniques to ensure ease of accurate data input. Microsoft defines a Validation Rule as “An expression that limits the values that can be entered in the field”.
In this tutorial:
Required knowledge:
1. Scenario
A Validation Rule is applied to a field in a table to limit the type of data being input into that field. It is a good example of how a Limit check or a Range check can be implemented in a database.
2. Rule
- Open your Table in Design View
- Select the Field to which you want to apply validation
- Place your cursor in the Validation Rule input under Field Properties
- Create the Validation Rule
- Add Validation Text
Take the time to add explicit instructions as Validation Text levelled at users with little knowledge of Access.
3. Test
Validation Rules do not affect existing data. Validation Rules are inherited by controls on forms.
Test the following:
- Leave the field blank
- Enter
4. Validate existing data
In the case where there is existing data in the field, the new validation rule can be run against that existing data:
- Open the table in Design View
- Activate the Table Design ribbon
- Click the Test Validation Rules command button
5. Next steps
The CAPS CAT curriculum only covers the Field Validation Rule, however, Creating a Record Validation Rule in Access is super easy.
References:
- Microsoft Support. (no date) Restrict data input by using validation rules. Available at: https://support.microsoft.com/en-us/office/restrict-data-input-by-using-validation-rules-b91c6b15-bcd3-42c1-90bf-e3a0272e988d (Accessed: 26 December 2023).