Access 2007

Data validation

Data validation is a very important database concept. It is the process by which Access tests the data that is being entered into the database to make sure it is in an acceptable, or valid, format.

Imagine one of your database users has entered an order date of January 4, 2008, in the month/date/year format as 01/04/2008. Another user has entered an order placed on that same date in the day/month/year format as 04/01/2008. If the database is tracking all sales for the month of January 2008, it may not show both orders as placed in January, even though both were placed on the same date.

Access 2007 allows you to set field properties and data validation rules to force the person entering data to follow a specific format.

Data types and validation rules

Data validation begins when data types are set during the process of building tables and fields. For example, if a field data type had been set to Currency and a text value is entered in that table field during data entry, Access will not accept an invalid format and will display a validation error, like the one below.

Data Validation Error Message

Data validation is accomplished by setting data validation rules and other field properties for various fields.

Setting data validation rules

• In Design View, highlight the field that requires a validation rule.
• In the Field Properties section at the bottom half of the window, set your validation rule using the Expression Builder. The Expression Builder offers common syntax to set up a data validation rule.

  Use Expression Builder to Enter Validation Rule

  
  

  Validation Rule for Category Field

Validation rules work most easily with numeric fields. Rules for text fields require you to enclose each acceptable value inside its own quotation marks, separating them with Or, as seen above.

Validation text

Validation text is a specialized error message that you can set to have Access tell the person entering data the specific way you would like that person to enter it.

To set the Validation text, enter the error message exactly as you want it to appear to your user in the row directly beneath the Validation Rule row in the Field Properties section of Design View. For the validation rule that we set for Category, you would set the validation text like this:

Validation Text for Category Field

The following image shows the resulting error message that the user would see when the Category validation rule has been broken:

Error Message Showing Validation Text

Using field properties to ensure data integrity

Another way to ensure data integrity is by setting field properties such as Field Size, Format, and Required.

• Field Size can be set to hold a specific number of characters, up to as many as 255 for text fields. If you were using a text field to hold the two-letter state postal abbreviation, the field size could be set to 2 to ensure that no one enters a full state name into this field.
• The Format field property can be set to display text or numbers in a standardized way. For example, text can be set to show as all uppercase, and numbers can be set to show scientific numbers, percentages, or decimals.
• Set the Required property to Yes if you want the person entering data to be required to enter something in the field. Choose No if the person entering data is allowed to leave the field blank.

These are just some ways Access helps you ensure data being entered into your database is valid.