Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Validation Rules check your data for accuracy according to your company's standards and the needs of your database. There are two kinds of Validation Rules: Field Validation, ensuring that the correct data is entered, and Record Validation, ensuring that multiple fields in the record work together harmoniously. Let's open up the Orders table and click in the Quantity field. Go up to Table Tools and the Fields tab, and then over here on the far right is the Validation button. Choose Field Validation Rule.
The Expression Builder opens. Type >0 AND <72 and click OK. That means a quantity has to be entered, and it has to be less than 72. But now that we've restricted what numbers can be entered into the field, it's important to let your users know what they can and can't enter. There's nothing more frustrating than not being allowed to enter what you are trying to type, and not knowing what you can type. So click on the Validation button again and choose Field Validation Message.
In real English, tell you user their allowed values. "Quantity must be greater than 0 and less than 72." Click OK, and then close your Orders table and Save it, if needed. Now open up the Products table, and let's do the same thing in Design View. Click on the Size Field and come down here to Validation Rule.
Here is where we are going to list our bottle sizes. We'll type =8 or =16 or =32 or =64 or =128. It's important that your Equal signs, spacing, and ors are all right. This works exactly the same with Text values. Access will automatically add quotation marks around the words.
In the Validation box right below, enter "Bottle ounces must be 8, 16, 32, 64 or 128." Go ahead and Close your Products table, and Save it if it asks. Next, let's enter in a Record Validation. This allows you to compare two field values and bring up an error message if the combination doesn't work. For example, our Sales Reps termination date must be after their start date.
Open up the Sales Reps table in Datasheet View. Go back to the Fields Ribbon, click on Validation, and come down to the Record Validation Rule. In the Expression Builder, double- click on EndDate - you might have to scroll down - and type a greater than sign, which for dates means after and double-click on StartDate. Notice that Access put square brackets around the field names for you. Click OK. Now, Save and Close your Sales Reps table.
We will test all these Validation Rules in Chapter 4 of this course. By setting Validation Rules, you'll greatly reduce human error during data entry.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86670 Viewers
80 Video lessons · 135980 Viewers
59 Video lessons · 54716 Viewers
52 Video lessons · 68618 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.