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 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 harmoniously together. Let's open up the Orders table. Let's say none of our customers will order more than 100 of any item, and we want to make sure that no one accidentally types in more than that. Click anywhere in the Quantity field, go up to the Table tools, Field's ribbon and on the far right-click on the Validation button and choose Field Validation Rule.
The Expression Builder opens, type in greater than zero and less than or equal to 100, and then click OK. I immediately get a message saying that existing data violates the new settings. Do I want to keep testing with the new setting? For now I do. This Window is telling me that some of my data violates my Quantity Validation Rule. I will have to go back and look for errors with less than one or more than 100 bottles. And either fix the error or change my validation rule, because it's not quite right.
I would say yes, now click on the Validation button again and we need to put in a field validation message. 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're trying to type and not knowing what you can type. In real English, tell your user their allowed values. Quantity must be between 1 and 100, I'll click OK. You can also set your validation properties in the Design view.
I'll use the first button to switch over and click in the Quantity field, and down in the field properties, I can see the validation rule and the validation text that I typed. Go ahead and Save your Orders table, right-click on the tab and close it. Now 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. Open up the Sales Reps table in datasheet view, I'll scroll to the right. In our example an employee's termination date must be after their start date.
Click in the End Date field, go up to the Table tools, field's ribbon and click on the Validation button again, this time choose Record Validation Rule. In the Expression Builder double-click on End date, type a Greater than sign and then double-click on Start Date. Notice that Access puts square brackets around the field names for you. Click OK, and again keep testing with the same rule.
Go back up to the Validation button again and this time click on Record Validation Message and type in English 'End Date must be after Start Date' click OK, and save and close your table. Let's test both of these two validation rules out. Open up your Orders table and we know from an earlier error message that we had a bad value. When I look through looking for anything less than one and greater than 100 I see down here at the bottom that there's no quantity entered. I'll test to my rule out, I'll type in 101 and when I hit tab, sure enough I'll get my error message with my validation text.
I'll click OK and I'll change it to a valid number. Now open up the Sales Rep table again and scroll over to the right-hand side, enter in an end date for our first record so that it's earlier than the start date. When I click off of the record, it will test the validation rule and sure enough it tells me that the End Date must be after the Start Date. I'll click OK and make the change, when I go to the next record, this time it accepts it. By setting validation rules for either individual fields or for the record itself, you'll reduce human error during data entry.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.