Data validation in MS Access

Amongst the many topics that will be covered by a Microsoft Access training course, one of the key subjects will be data validation. Validating data in MS Access allows you to check data whilst it is being entered by the database operator and will prohibit the data from being saved if it breaks any validation rules or routines.

Errors and omissions easily occur when data is being entered by a user, so the need to check the data when it is being entered is important. Allowing invalid data to be entered into a database ensures that the database and the data available will become less valuable or corrupt, and it will be more difficult for the user to make use of.

The two most important types of data validation are:

Field level validation

Field level validation allows for data validation in an individual field. MS Access will not allow you to store the incorrect type of data in a field if the data type is not appropriate for the field's pre-defined/ built-in data type.

For example: a field that needs to be filled that refers to 'Name' could have the following validations: it should not be left blank; it should exclude numbers or other special characters; it should have a maximum length. These validations are performed to ensure that incorrect information is not accepted by the database.

You would choose field level validation when:
- You require a specific type of information to be entered into a field
- You want to validate the data entered into a specific field independently of any other data in the database.

Record level validation

Data is validated from more than one field with reference to each other so as to ensure correct data input. An example in an 'employee details' database would be where the retirement date of an employee cannot be the same as the start date, or in another database where a value in a field is always greater than another in the same record.

You would choose record level validation when:
- You want to limit or control the type of data a user can enter in a database or record
- You want to compare the values of two or more fields in the same database or record to ensure they comply with the rules for the database

Microsoft Access provides several methods of how to check the data being entered, two of which are the ValidationRule property and the Required property.

ValidationRule property

This can be used to specify requirements for data entered into a record or a field. If data entered violates the validation rule a specific message, set by using the validation text property, can be displayed to let the user know of the incorrect entry. If the validation text option is not selected a standard error message will be displayed.

Required property

This function can be used to specify whether a value is required in a field, thereby ensuring that the field to be filled in is not left empty. MS Access will display an error message when a required field is left blank or data is removed from a field where data should be entered.