Well as the decibel Welcome back. In this video, you'll learn that data validation to a pre existing database data validation list of define what type of data you want
You can set up data validation to prevent users from entering data.
And that isn't valid and you can write messages to define what input you expect the instructions to help users correct any errors.
The database will be broken, working with is working for a small clinic is broken up into two tables. I've got a patient table and a therapist table. So these are the patients in the survey the doctors
Let's go ahead and start off with the patient table and the first data validation will work and what is the gender right now.
It's limited to one character. Yes, but maybe someone just got work finish work man I kindergarten, they would use the algae.
Or boy, girl, for the gender. Well, that's fine. But now all of a sudden it's like you go ahead and create or and you're trying to find all your patients that are female.
And it's not and you're going to enter them in this particular person would not show up. So we're going to make sure that
Not only it's one character, but they only character can be either that or now. So let's go ahead and get started with that the press on.
The design view nice first as Robin mentioned is that this database can be found in the same area as you found a link to this instructional video
So first up yours. I'm gonna go to the design view. I'm going to select gender and I'm going to come on down here on the validation wrong. I'm just going to put out
For him then double quotes. I tell you what, it's kind of hard to see. So let me just put that in a little bit in the database here.
Here's one of time either an F, but then double quotes or
OK, and then it a little text here to help the user, please. Then turn either you actually paste that same thing. There have program in the
Copy this and showed up here and I'm worth bad, just so you can see a little bit larger there.
Okay, so I've got a validation rule in it that is invalid than this text will appear.
So at this point, let me come along and I'm just going to change the data sheet view which point that's going to prompt me to save it. Yes.
It's now going to look to see if any data integrity rules are being violated. I'm going to press. Yes. Here in the state basis so small. It's going to go through pretty quickly. Okay, so now I come along and change this to a G kind of the cell, I get a little message, please send her at all.
Okay, so go along and change that back to enough again this way when I do a report, and I'm going to find all males or females. I'll make sure that I'm pulling up everyone, as opposed to if someone actually be for point
Okay, the next one we're going to work. What is on the stage. The first thing I'm going to do is I want to make sure that this, you're gonna say, I'm going to show you the really tricky here that someone doesn't enter a zero instead of it. Oh, there is the zero to midnight to the pH.
Okay, very close to zero in the liquid back to wait for Ohio.
Okay, so the first step I'm going to do is to change the validation to make sure that zeros want
To do that I can come up here. Good into the design view that was of the state.
And under the validation rule. I'm gonna do this was the first thing here, I can type in string.
Parentheses square bracket.
Square bracket.
State.
Square bracket.
I'll explain this here in a second, let me first of all give this where you can actually see it here. So let me I go ahead and select that copy it and
Paste. So what's going to happen is we're going to be doing an industry, we're going to be looking at state and we're looking. Actually, I wanted to put a zero here.
So I need to modify it online as well. So unattainable. Right.
Yeah, we'll go with the zero so we better find a better not finding zeros, and if we do, we're trying to address any value other than zero position is, other than zero, we're going to return a message there. So let me make sure to let me go and copy mine.
And paste it.
Okay, and then I'll put no
Zeros around. Okay, so first step I'm going to do is eventually we're going to simply limited to a through z and I'm kind of break it up into two steps. Let me show you. Okay.
The first step is we're not going along zero. So let me come along and check this out. Let me check on the chase the data sheet view. Yes, I'm going to say that and yes to data integrity. OK, now let's come along and this change that to zero, H.
And as soon as I do that I can see. I know zeros allowed. So let me go ahead and fix that they can show that that's a capital O, capital H for Ohio.
Okay, let me show you another way of doing that. And I probably should have showed you the first of its way I'll show you one more way in rather than just not allowing one character. That's the only allow a through z, this would be a little bit more involved.
You know what makes sense if I type of here first.
Okay, there's no
Not like
Square brackets explanation point is going to be
A to Z.
Square brackets Asterix and devil, quote, okay, it is not so even if it's not, it's fine or not, like, and I'm going to put the double quotes the star mentions or more characters, not a through z Asterix except for them. Okay. Let me go ahead and copy this.
And again, this is going to go on the stage validation rule. And if you hear, I'll put
It through z.
Characters
Wow.
Okay, see I can actually place this information. So you can read that as well.
Let's go ahead and check this out on the come up here, press on my data sheet view it. Yes, yes. And now what I'm going to do is come over here and maybe rather than Michigan. I'm going to type a one.
And then here was the AI when we put capitalized. It's hard to see there, but then let's enter a capital and I think you might see the one. And as soon as I press on others. So it's
Only a through z characters allowed. So now I'm going to go ahead and put that back in
So now what I've done is I prevented people from accidentally entering numbers. They're like Michigan being in one
Ohio. There's no way you could enter a zero. It's got to be enough. So now I'm going to do a search later on and I searched for all the patients that are from Ohio, it's going to pull them all up.
As opposed. If I had entered at zero. He would not hold that particular patient. Okay, we're gonna do about one more with the patient that is right now. For example, I could put a negative five here, click on a cell. And sure enough, it's going to take it or maybe I could put
500,005 and it's going to take it. Let's just go ahead and limit.
The therapy. Number two, between zero and 50. But to do that, I'm going to come up here to the design view, just like we've been doing. I'm going to press on the therapy number and the data validation roundtable where I haven't done it.
I'm gonna go ahead and something greater than or equal to zero and less than or equal to 15
So my copy that.
That's going to go into the data validation rule. And I'll paste and then here I go.
Please enter a number
Between zero
And I'll go ahead and copy that.
And paste
Okay, let's go ahead and check it out on the come up here persona data sheet view yes twice.
And now let me put a negative here. Let's say I accidentally put a negative five. Whoa. Okay, now get a little warning message here please between zero and 50
And effect. I have five accidentally put a minus sign for that it's going to notice that and help staff.
Okay, that was the patient. We're gonna do one more with the therapist. This will be a little bit different here.
Maybe there is some type of membership certification. Here's one that was started. Here's one at the end date on it.
Maybe some deployment day. What we want to do is make sure the end date is greater than the start date. So right now, which is my foot 2013 doesn't complain and we wanted to complain when we get a the end date is less than the started. So let me put it back on Sunday, greater than 2014
Okay. To do this, I'm going to go ahead and first of all, just like we've been doing. We're going to go to the design view.
It, but here I can only be working with one at a time. I could work the start date or in day. I want to be working with those to start and end date.
So in that case, on a personal property sheet and over here on the data validation. Let me do that like you're an inner right here on data validation or we come down here and I'm going to put just again date.
Square brackets. You better be greater than or equal to the start date.
Okay. And let me grab that copy a
validation rule. I'm gonna taste that attacks. Maybe one day.
Let's be greater than start
And I'll go ahead and grab that
Copy and
Paste we give this to simply say, you can actually see what I'm just type them. Okay, let's go ahead and check that out. Go ahead and just come up here, press on. Does she knew yes and yes okay now let's try changing this to some year less than 2014 to 2012
And as soon as I do it. Oh, and they must be greater than started. So maybe I accidentally hundred incorrect and then at this point I can change that to some year greater than 2014 but hopefully this video you learned about data validation now kitchen next video. Thanks.