Data validation rules

- [Instructor] Validation rules allow you to control what data gets saved in the database. If you try and enter in a value that isn't allowed by the validation rule, access will display a warning and allow you to fix the problem. Let's put some validation rules into effect in out CreditCards table. I'll go ahead and open it up in datasheet view so we can take a look at the columns that we want to affect. First, I want to make sure that the expiration date is either today's date or something in the future. I don't want the ability to enter in a credit card that's already expired. Next, let's supposed that the hotel is only able to accept MasterCard and Visa credit cards.

We can create a validation rule that ensures that we don't enter in a card from an unsupported merchant. Let's go ahead and make those changes in Design View. To get there, I can either use the view button here on the home tab, or I can right click on the name of the tab here at the top, or I can right click on the name of the CreditCards table here in the navigation pane. Either way, let's go ahead and switch into Design View, and we'll start making those changes. First, let's do the expiration date rule. I'll go ahead and click on that field, and that'll update the properties down below. Then, we're gonna find the validation rule property here. I'll right click in this box, and then go to the far right, and press the build button.

That'll bring up our friend, the Expression Builder, again, and we'll type in a code here at the top that says greater than or equal to, and then one of Access's built in functions, the Date function. I'll just type in the word date, an open parenthese, and a closing parenthese. The Date function simply returns the current date in time. If you're familiar with how functions work in Excel, this is the exact same as the Today function over there. So what we're saying is that, whatever value gets entered into this field when adding a new record, must be greater than or equal to today's date, on the day that the date is getting entered.

I'll go ahead and say okay to save this rule. That gets updated down here in the field properties, and then we can turn our attention to the validation text. This text will display if someone enters in an invalid date, instead of just getting a generic error message. This could be helpful to anyone that might need some additional help to know what's a valid value. Here, I'm just gonna type in, the credit card that you've entered has expired. Next, we'll enter the card type validation rule to only accept MasterCard and Visa.

I'll go ahead and select that from the field up at the top, then we'll come back down to the validation rule, I'll click there, and then click the build button to the far right. This time in the Expression Builder, we're going to accept some literal text. So it needs to be equal to, and then in quotation marks, MasterCard, closing quote, or it also needs to be equal to Visa. So, in quotation marks, Visa. I'll say okay to save that validation rule, and on this one, I'm gonna leave the validation text blank, so that we can see what the default error message looks like and compare it to the one that we typed in for the expiration date.

At this point, it's a good idea to save our design changes, so I'll press control S on my keyboard. It's gonna tell me that the data integrity rules have been changed, and that existing data might not be valid to fit the new rules, and that's okay. It's not gonna go through and check the credit cards to see if they're currently expired. It's just not going to allow us to enter in any newly expired cards. We'll go ahead and say yes to that, and our table is now saved. Let's go ahead and switch our view into datasheet view and test everything out. I'll come down to record number four and we'll start typing in some things. For the card name, I'll just type in Business.

We'll type in a valid card type here, a Visa, a card number, and I'll type in an expiration date far into the future. I'll press tab a couple of times, and Access is telling me that I can't add or change a record because a related record is required in the table Guests. And that's because I didn't type any valid Guest ID here. So let's go ahead and just attach this to some random guest, how about number 12? This time I'll press enter, and that record gets successfully entered into the table. It didn't have any problems. Let's try entering in one with a different value.

Once again, I'll type it in as a Business card. This time, the card type, I'll say Discover instead. When I try and get out of this cell, Access is telling me that one or more values are prohibited because of the validation rule. It needs to be either MasterCard or Visa. Let's go ahead and say okay, and I'll make that change. Switch it over here to a MasterCard. This time, it allows that value. I'll go ahead and type in a card number, and I'll type an expiration date that's already expired. Once again, I'll press tab, and this time we see our custom message instead of the default warning.

And it just tells me that the credit card that you've entered has expired. Which is a lot more friendly of a message. Gonna say 'kay to that, and I can change the date. Finally, I'll type in another Guest ID, how about 13 this time, and that card finally gets saved into the database. So validation rules can act as gatekeepers to make sure that you can trust the data that's being saved into your tables. I'd recommend that, any time you use a validation rule, you should always also add a friendly reminder in the validation text property. It provides additional instructions to the person that's typing in the values, so that they know how to fix any problems with their data entry.