Validation rules and defaults

- [Instructor] Now as often happens as we build our tables, I forgot one of my fields. I wanted to keep track of the status of my customers and I wanted that to appear here before I got into these specifics about the customers. So I'm gonna select the field that should follow the one that I forgot, click on that field, and go up here and hit Insert Rows. And it gives me a new row. And this is gonna be the Status field. Now in the Status field I'm gonna keep track of whether the customer is an existing customer, a customer I used to work with but they haven't been buying for a while, or a customer I'm trying to turn into a customer.

So I'm gonna go over here to the Data Type, and oh, lo and behold, it's a Short Text field, which is the default, and I'm gonna leave it that way. Now what I'm gonna set for this field is what's known as a validation rule. And what a validation rule does is it forces the person doing data entry, whether that's you or somebody else, to stick to a specific list of values. And so it prevents them entering anything other than the values you specify. It also gives you the ability to put in an error message so that they just don't get stopped and go, "Why isn't this working?" They get stopped and they know why the system is saying, "No, no, no" and it explains because you'll have typed the explanation why they have to stick to the rule.

So while I'm on the Status field, and make sure you pay attention to which field you're on when you go down here and work in Field Properties. A really common mistake is to not pay attention to which row or field is in play and therefore apply the rules to the wrong fields. So I'm gonna make sure I'm on Status and I go down to the Validation Rule row. Now all I have to type is a series of values each separated by the word 'or'. So the three status values I've decided to use are Current or Inactive or they're a Prospect.

Now as soon as I go down to Validation Text, you'll see that Access puts quotes around the words and capitalizes the word 'or'. Once you see these things start to happen as you're building field settings, don't fall into the trap of trying to put those in for Access. Let Access add quotation marks, capitalization, other words, symbols, etcetera. Let it add that stuff for you. So don't type them in. Jut type them as I had originally done. Now here's totally up to you what you can use for your validation text.

You keep your corporate climate in mind as you do it if you're doing this at work. If it's for your own use, however, you want to be reminded. But I'm just gonna be simple and polite here and say this field, because you're gonna be getting the error message on a field by field basis here, you know? Just with each record. This field can contain only one of a series of approved values. Please enter. And then I'm gonna list them since it's a short list. Current, Inactive, or Prospect.

Thank you. 'Cause this is gonna appear on screen in a prompt box so you want it to look like an official prompt coming from the operating system. So we have the validation rule is set to those three possible words, we've set up the prompt that will occur if someone breaks the rule. And we're all set. So if I actually switch over to Datasheet View, and, yup, I'm gonna save the table. And let me just go over here to Status and attempt to put something else in here.

So if I type Old Customer, there's my prompt. I click OK and then I'm gonna change that to Inactive. And it's perfectly happy. Now the last thing I'm gonna show you here, and this relates to data consistency as well. I'm gonna right click and go back into Design View and I'm also gonna set a default value. Most of my customers when I add them are gonna be current customers. It's a good thing, right? So I'm gonna type Current here as the default value.

This will save me a lot of typing because as I enter records, if most of them are going to be current, I don't have to type that myself. Let me switch back over to Datasheet View. We'll add another record. And so this record... Notice as I scroll over. There it is; Current's already in place. So default values are great when you have a value that's gonna be appropriate, say, 75% of the time. You can always change it. You can always put in something different. For example I can turn this one into a Prospect.

Notice the New Record row down here already has Current in it, but it accepts Prospect. So default values aren't carved in stone. You can change them. And validation rules restrict people to a specific list so you know your data is reliable. So between validation rules and default values, you've got at least two of your fields locked down so that you can rely on the values entered. Which fields is this gonna help in your database?