Add validation rules and input masks

- [Instructor] Knowing how to control the data being entered into table records, is important to ensure that valid information is being entered into the table. After watching this video, you'll know how to set up data validation for your fields and your records. You'll also understand how to use the built in input masks. Let's jump into our data validation database and take a look at our employee data table. We'll go into Design View, and let's first take a look at our Date of Hire field. I want to ensure that no Date of Hire prior to today, is entered into a record in this table. So, we're going to set up a data validation rule that any new records going forward must meet the requirement of being equal to today, or being greater than today's date. We'll go down to the properties for the date of higher field, and type in our validation rule. Greater than or equal to, and we'll use the Now function, which returns the current date and time. Notice as soon as I start typing at the auto-complete, pops it right up, and we created our rule. Now, it's kind of unfair to create a rule and not let your users know what you want them to do, it can be frustrating if you don't add some text. So, I'll type in DOH for Date of Hire, must be greater than or equal to today. Now, keep in mind this is only for new records being entered going forward. So, this validation rule that we just set up is only applied to this field, Date of Hire. And that's why we were able just to put in greater than or equal to now. Let's go out into our Datasheet view, and I want to show you how you can set it up for the entire record. I'll click on the DOT field, go to Table Tools and Fields, and here's validation option we have. So, Field Validation Rule, that's what we just set when we were in Design view. If you want to set it for the entire record, you would use this validation rule. This is where you can compare two fields together. So for instance, Date of Termination, I'll just double click on these fields to pop them up into the expression builder. It's got to be greater than the Date of Higher. So, I'll go ahead and double click on Date of Hire. So, we're comparing two fields, and that's why we want to use the record data validation, so we can look at both of those fields in the record. I'll click Okay, here's the error that existing data violates the new record validation rule. Well in the DOT field, we don't have anything entered yet, so it's really bouncing it off of that, so we're fine. I'll say yes, and let's enter a date of termination that is prior to our date of hire, 1/1/2009. So, it looks like it took it, because remember it's looking at the entire record. But, as I tab on through, when I get to the end of the record, which is where Access would save the data, now I'm getting the message that there's a validation rule set up for the Date of Termination field, and my data I entered does not meet the requirements. I'll go ahead and click Okay, and I want you to keep in mind... Oh, it wants me to change that. So, let me hit my Escape key on the keyboard, and then we'll remove the date that I put in for Date of Termination. If we go back to our Validation drop down, this is where we can enter a message just as we did when we were in Design view. So we can say Date of Termination must occur after Date of Hire, and click Okay. And this time if we enter 1/1/2009, and tab through to the end of the record, now we see a really nice message that pops up. So, those are the differences between setting a validation rule for just a field, and setting a validation rule for the entire record. Let's go back into our design view, so I can show you how to use these great built in input mask. For our Date of Hire, I want to make sure that the dates all are formatted the same say, not matter how a user types it in. So, with that field selected, I'll come down to Input Mask, go over to the right hand side and click on the Ellipses. I'll save my table, and these are the masks we have available for date and time. And you would see different ones available if you were typing in phone numbers or zip codes. So, let's choose the short date, which is the month, the day, and the four digit year, and click Next. Here's the Syntax for that mask, I'll click Next. And Finish. So, we have now applied that Input Mask to our Date of Hire. I'll go back into Datasheet View. Let me change this date, 7/26/2010 to July. See how it's stopping me? It's not going to let me type that in, so that mask is working really well. But, if I go 07/26/2010, it works perfectly. So the rules you set up can affect you as you're testing this out too. It's nice to know it's working okay. I'm going to go ahead and click Okay. So that date that I typed in doesn't meet the validation rule that I created. So that is how easy it is to control the data that's being entered into your records and your tables, so you can make sure that the data will be formatted correctly, and will meet the validation requirements that you have created.