Table Design   «Prev  Next»

Lesson 2 Setting up validation for fields
Objective Use the Validation Rule property to test data.

Access Rule Property Data

Setting up Validation for Fields in MS Access

Because data in database is the key word, it is essential that you make sure the data that comes into a database is correct. This is where the Validation Rule field-level property, otherwise known as a field property, comes in.
The Validation Rule field property is a feature that lets you place restrictions on data as it is being entered into a field in a table or a control on a form. Navigate through the Slide Show below to see how this feature works.

Validation Transcript
In the validation rule used for the table displayed in this SlideShow, the data entered into DateWorked field will be compared to the current date using Date() function.

Validation Rule property syntax

The syntax for the Validation Rule property looks like this:
<= is the operator.
Date() is the value to compare.

For operators, you use the standard =, <, >, <>, <=, and >= characters. The value to compare can be either a literal value or a returned value, as we haved used with Date(). Here are examples of Validation Rule property settings, along with their real-world descriptions.

State <>"WA” State can’t be WA
ShippingDate >=Date() The value entered in ShippingDate has to be today or in the future.
Amount >100 The value of Amount has to be more than 100.
Hours >0 Hours that are entered have to be greater than 0.
NumOfKids <=4 Only four or fewer kids allowed.

Validation Rule Property
Be sure to take advantage of the Validation Rule property at the table-field level. It will aid you in your efforts to keep erroneous data out of your database.
In the next lesson, you will learn how to set the Validation Text property to display a custom message.

Adding Validation Rule - Exercise

Click the Exercise link below to practice using the Validation Rule property.
Adding Validation Rule - Exercise