How to Prevent Bad Data from being Saved in Access Tables by Using Validation Rules

In this article we look at why validation rules are so important in keep the data stored in MS Access in pristine shape

Avoid Bad Data From Being Saved In Access Tables By Using Validation Rules For TablesMS Access allows the users to manage data in the most convenient way but it’s often observed that accuracy remains a challenge for bigger databases and yes we do end up with Bad data. Also known as dirty data; Bad data is any information that is misleading, erroneous and lacks formatting, causing serious damage to the data purpose. The problem comes in picture because of errors usually caused by missing/empty fields or inaccurate/duplicate data.

Validation

Bad data can be prevented by certain validation rules in Access, which more or less appear as criteria in a query. Users can create general rules for the fields, or they can go to properties box to create rules for the tables.

Validation rules are very helpful and often necessary, but they are absolute in nature so user must be careful before applying them. Let’s discuss the scenarios where validation rules could be used.

When To Use Validation Rules?

These rules are applied to avoid bad data. For example birth year of employees, it is entirely possible that small manual errors could occur while filling the data like if the user enters 1880 instead of 1980. In such cases, Validation rules could be applied. Users can therefore restrict the fields by setting a year mark, say 1960, and any value out of the set will be termed as invalid or would be blocked.

It is advised to comprehend all sorts of possibilities before attempting to block the data to avoid unnecessary data restrictions.

How to Use Validation Rules

Validation Rule In AccessValidation rules are of three types, viz. Field Rule, Record Rule and Validation on a Form. Validation can be used to specify a criterion of field or a condition for all the records.

Users can select a field in their table design, and in the lower pane, there is option of Validation rules. The option allows users to validate rules for fields.

For example if a user wishes to have a value of strictly 3 digits, validation rule can be applied as ‘Is Null OR between 100 to 999’ for number fields or if the user doesn’t wish any future dates in the fields, then he or she can use the rule for field with ‘Is Null Or <=Date()’.

To validate rules for tables, users can open the Properties box from Table design to find validation rule. The rule can only be applied once the fields are filled with data and the rule can be used to draw comparisons of values across the fields. For example if the user wishes to make a rule that if one fills Field 1, Field 2 must be filled too. The validation rule for table would follow as ‘([Field1] Is Null) OR ([Field2] Is Not Null)’

MS access doesn’t allow null values in records if the user applies the validation rules. So to avoid perilous errors in your database, better add ‘OR is Null’ to the rule for field. Higher versions of Access however accept null values.

Over time despite best efforts to keep an Access database optimized, you may still end up with a corrupted mdb file. In such scenarios you should immediately opt for a proven Access recovery tool.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server repair and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.