Control input with masks

- [Instructor] Access databases give us lots of ways to control the input of data into the tables. This helps ensure that what's entered is typed correctly, and often more importantly, consistently. We can often make use of the input mask field property to help make sure that every time we enter in a phone number it gets formatted properly. Let's take a look at this with our guest table, and I can open it straight into design view by right clicking on it in the navigation pane, and choosing design view from the pop up menu. We're gonna focus on the phone number field, so go ahead and click on it, and that'll update all of the field properties down below. The one I wanna take a look at is the input mask which is the third one down from the top.

I'll go ahead and click in the blank cell to the right of input mask, and that'll then display a button way over here on the far right with the three dots on it. This is called a build button, and we're gonna see this button appear in lots of places throughout Access. It indicates that we're about to build out a property definition. Lets go ahead and click on it, and that'll open up the input mask wizard. On the input mask wizard we see lots of pre-defined input masks for things like phone numbers, social security numbers, times and dates, and so on. In the try it box below it'll give us an area to test drive the selected mask.

Right now, I've got the phone number mask selected, and in the try it box you'll notice that it automatically puts in the parentheses and a hyphen, and it gives me blank spaces to type in some numbers. If I type in a bunch of numbers, you'll see everything gets formatted properly. Lets go and backspace off of that and try typing in letters instead, and you'll see that those don't get put in. The input mask is allowing us to type in the numbers and formatting them properly, but it's not allowing us to enter in any text. So we're gonna use this as the basis for our phone number mask. Lets go ahead and select it and press the next button. Here, we can edit the rules that the mask is written with.

Reading it from left to right, we start with the exclamation mark. This means that the number is gonna be entered in from left to right, rather than from right to left. Parentheses and hyphens are literal characters, they're just gonna represent themselves. The number nine indicates that we're going to be putting in an optional number, and the number zero indicates that we have a required number in this location. Now our phone numbers for the hotel are going to be serving an international clientele. So in this database we also want to make sure that we're storing the country code before the area code.

To do that, we can just simply edit the mask. Right after the exclamation mark, I'm gonna type in a nine and then a hyphen. Then we'll type in the open parentheses and the code as it was before. So three nines, a closing parenthesis, three zeros, a hyphen, and four more zeros. Below that we can also change the placeholder character. The default is an underscore but using the dropdown menu it can change to something else. Perhaps we'll use the pound sign or the number symbol instead. Lets go ahead and go to the try it box and we'll see what that looks like.

Now we can type in a phone number that matches the format for the Landon Hotel including the country code and area code and then the rest of the phone number. Lets go ahead and press next, and we get to choose whether we wanna store the data with the symbols or without the symbols. I'm gonna choose to store the data with the symbols in the data table. I'll press the next button and that's everything we need so I'll press finish. In the properties we can see the input mask has now been put in. Now it looks a little bit different than what we just saw and it's not really important that you be able to read these symbols, but just for those of you who might be curious, the quotes indicate literal characters, the backslash also indicates that the next character is a literal.

At the very end we have a semicolon and then a zero, and that's a flag for Access that just indicates that we're going to be storing the punctuation in the table, and then finally we have a semicolon and the pound symbol, which is the placeholder character that we chose. So lets go ahead and switch back into datasheet view and see what this looks like. Once again we've made a structural change to the database, so Access is gonna prompt us to save this side of the table. Go and say yes, and that'll switch us to datasheet view. Now we can go up and edit one of our phone numbers. Let's just make this column a little bit wider, and I'll choose the first phone number here and backspace off of it.

You can see that the pound symbols are getting filled in instead. And now we can just type in the phone number again and I don't have to type in any of the punctuation. So that's how our input mask is going to work. That makes sure that all of our phone numbers are gonna be formatted consistently. To get more information on all of the input mask placeholder characters and what they mean, just go ahead and go up to the help menu, I'm gonna start up the help pane over here on the far right, and I'll do a quick search for input mask. The first entry, control data entry formats with input masks is the topic that I want, and if I scroll down we'll find a table that displays all the different characters and what they mean when building out your input masks.

So using input masks, where appropriate, is a great way to make sure that the data that gets entered into your database is consistently formatted in the same way, no matter who sits down at the keyboard.