Microsoft Access 2016 Data Validation Using Masks Types And Expressions Video Tutorial
The way to ensure that data entered into Microsoft Office's Access database is by using data validation. This can be done by choosing the correct data types and also limiting them. Then we can use Data Masks to ensure date is entered in a certain formatted way. The powerful way to ensure that date is correctly validated is to use the Expression Builder. This video gives an introduction to the above.
Transcript (machine generated so it contains errors)
Hello. This video is all about. Basically, how to ensure data entered into your access database is correct, i.e. validated and you restricted to certain types it's an introduction video so it should be a short one. Let's have a quick look that starts a new blank database make it larger okay that as you can see when you get your tables you have certain fields, you can go into the design view and was savoured as skipper said one if we going to design view, you'll see the kind of types that it is okay for example, the first one is not a number datatype automatically increases numbers and it takes a long integer format. Let's look at some other data types. For example, over here.
Let's call it currency okay and that will then ensure that you are entering numbers, so I'll just call it currency given a name now over here. If I wanted to put in a three for PC. It says the value entered does not matter currency datatype in this column so by choosing the datatype itself. You've automatically created a certain catlike verification system to ensure that users do not cause problems by entering incorrect data there and let's go over here take shall we say short text now short text is 5 cups or less extra then at the front, but now if we get the design view, you'll see our short text which will just change the name of their and basically you can limit the size of the text of it. A gate that will be two and 55 characters. Let's bring it down to shall we say, three, so it is simple. One and let's go back elastic to save. Yes, it'll say some data may be lost because basically if you are worth doing this at the beginning of creating our table, so it's a problem, but let's assume you already created a table and you had shall we say to 55 letters in those fields, they would be now reduced to only three, so you'd lose a lot of those o despair that in mind if you do changes later, I can so now we have our short text. Let's try and enter more than those three characters.
For example, hello see if it works it stopped at the first L didn't allow anything the keyboard typing away. It stopped so that is also a way to validate entering of data into it. There's other data types. Okay, you can take for example yes and no. This is basically yes or no. Nice to stick them together because sometimes spaces cause problems if programmers are trying to access feels okay, now what yes I know is is basically a Boolean which is true or false, so you take it, it means it's yes it's on. Don't get it means now. Okay, so that's another way of validating itself. For example, if you want it create shall we say is customer currently active okay. You could have a Boolean over there, yes and no. So that's also restricted so people don't type. Yes, don't take no somewhat have small sum would have a big letter and would cause a little bit of confusion set. I would suggest that when we are going through their we look at basically the different kinds of feels that you can okay all sorts of fields. Attachments can be files, pictures, okay, am hyperlinks would be a link and then you basically have all these other extra types.
Okay, so now let's look at another way of formatting our texts to ensure it's validated and it's not entered incorrectly, phone numbers and email addresses are typical catlike examples. Let's start with a phone number. Okay, so let's give it a number and let's call it phone number. Now, the way we can do this is actually by using an input mass okay, let's assume you wanted to have the first three numbers as an area code and that needs to be entered. So for example, how would do that word have a\in our input mass and then brackets and bandwidth have three numbers and then we basically close enough. Simple as that. Okay, now let's try another type using a phone number and date where we need to specify how many digits and an area code to get them way to do this is actually by using an input mass and will switch to design mode. Okay for our input mass. Let's assume we are going to have an area code surrounded by brackets.
Okay, now to ensure that certain characters are coming in the number box. We need to actually have a\so that a\and and that will ensure that our (actually shown and then let's say it needs to be three digits so #73 digits and then once again\we close off (and then let's say we need seven digits after our area code. Let's have our #123 4567 let's test it out need to save it is fine for a phone number. Let's entering as you can see it needs three digits in the area code, so let's for example, 203 and then seven digits so let's say 123 and then 4567 and if I try to enter more digits. It doesn't allow me so that has restricted us to entering phone numbers in a certain fashion which is a very good way by using an input mask. There are special characters that you can use in input mass. For example, you can use and symbols which means it's a required character. If you put like a? It's an optional character. You can have wildcard characters, which is the star I would suggest you actually do take a quick look online okay for input mass special characters, and you'll find a list of them again, but this is a very simple, straightforward way of gritting a phone okay, now we come to another type of validation which uses expressions okay, let's say email.
Email is validated, some sort of text then you have an at symbol then you have some more text and then it's a. And then it's like.com. The more text okay so let's give this short text but squalid email K. Now we go to our design view, and our validation rule. This is where we need to be okay click their and gives us an expression builder. This is ackee quite a complicated topic, but were just giving you a general introduction, so the first thing is were trying to find it. Like what I said some characters followed by an at symbol some or characters followed by a. And then and some or characters so we go operator and then comparison so we want to make it like a certain expression so that and I'll just paste this in one very simple. I'll explain what's going on here. We have the speech marks which is indicating text. So basically, this is all gonna be text within your open speech marks and speech marks then we have our star, which is any character is the? Which is any character so we need these to then it's followed by N at symbol that we do need and then and any kind of characters then. And then any more characters that are very basic validation rule selects click okay and validation Texan gets we get it wrong. So, enter a valid okay, let's see.
br>Let's go back to Alaska to save it might say things like this don't worry because Warren created a few fields. That's why yet. Okay, so email us tested out to see if it works so formed.com is at work. Now enter a valid email address. Bob home let's say Windows media into and then we have..com. It accepts it so this is how we do data validation in access let's go here and for our phone number is of having them as the #if we actually have them as heroes. 123 4567 and then over here as well will have them zeros 123, bringing back save what we need to do now is let's just delete everything okay, so 203123456 were missing one you see it needs that extra character, so that's data validation for the vast majority of the simple basic task that will be needed. Hopefully this video is held
Visit our YouTube channel: https://www.youtube.com/channel/UCFj1BHYIUYfPWPb1Xn5qFIg
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.