Controlling input with masks

Access databases give us lots of ways to control the input of data into the tables. This helps ensure that what we've entered is typed correctly and often more important, consistently. We can make use of the Input Mask Field property to help make sure that every time we enter a phone number for example, it gets formatted properly. I am going to go ahead and open my Customers table and I will open it in Design View by right-clicking on it and switching directly to Design View. Now for my Phone number field here, down in the Properties, we will see the Input Mask property here.

When I click on it, we get a Build button way over here on the right side. These three little dots will indicate that we are going to build a property for this, and you will see this three dot convention in a lot of places throughout Access. Go ahead and press that button to launch the Input Mask Wizard box. Here we can choose from a list of predefined Input Masks. The Phone Number Input Mask is what we are looking for here. And down on the bottom we have a Try It box. What I want to do is click in the very beginning of that box and try typing in some numbers. You will see that it formats the phone number properly.

Go ahead and back off of that. Let me try some different numbers. It puts in all of the parentheses, the spaces, and the hyphen in the right spot. Go ahead and press the Next button to accept the phone number property. Now we can edit the Input Mask if we choose. Access uses a little bit of code to help tell it what's valid and what's not. In this case the 9s indicate that it has to be a number, but that it's optional. The 0s indicate that it has to be a number, but it's required. We can also change the placeholder character down here. Currently it's an underscore (_). If I use the dropdown list, I can choose a different character, for instance, this pound (#) sign and now in the Try It box when I click down there, you will see that it gets placeholders and are shown up with a pound (#) symbol.

Let's switch it back to the underscore (_) character and go ahead and press Next. Now we get to choose how we want to store the data in our table. We can store the data without the symbols in the mask, with just the numbers, or we can store the symbols in the mask with a parentheses and so on. I am going to choose to store the symbols. Go ahead and say Next and that's all the wizard needs. So we will say Finish. Access puts the Input Mask down here in our phone number field and we can go ahead and switch back to Datasheet View and test it out. Now because this is a structural change to the way the table functions, when I switch my view, Access is going to ask me if I want to save the table.

So go ahead and say Yes, and now that change has been saved. I will go ahead and go up to our phone number field here and I'll just highlight this first one and I'll delete it, and we will try typing in those numbers again. 383-835-9398. You can see that it puts in all of the punctuation properly, and I don't have to take my hands off the number keypad to type in the phone number now. Let's go ahead and create one more. I want to create an Input Mask that forces all of my state abbreviations to be capitalized. I don't want to have any lowercase state abbreviations.

Let's go back into Design View to do that. I will switch to Design View here and for my State Field, I will go to the Input Mask property again, and click the build button over here on the right. Now if I scroll through this list of available Input Masks that are predefined, you won't see anything about state abbreviations in here. So what we can do is say Edit List to add it. The Edit List field shows up and it's set up just like Records in any other table. So you can see the Input Mask Wizard properties are set up like a table.

I can scroll through the various predefined input masks or I can go to the very end to the blank record, which is number 5 here and put in my own. I am going to put in a Description here, State Abbreviations. The Input Mask here is going to be the greater than symbol (>) which indicates that everything I am going to type in is going to be forced to be uppercase and then I am going to put in two Ls. The Ls indicate that we are only going accept a letter. We won't be able to put in any numbers here. The next field is the Placeholder. This is what shows up we haven't typed anything.

I am going to put in an underscore (_) character. Then down below Sample Data, this is the example of what the output will look like. So we can see it when we choose it from the list. I will type CA for California as our example. Go ahead and say Close to this, and you will see it now at the bottom of our list, there. Go ahead and select it. We will say Next. We can see the abbreviation mask here, our placeholder character there. We can go ahead and try it out here. Now you have to click at the very beginning in order for this to work. So I will go to the beginning and we will try typing in two letters, and you can see that they're forced to be uppercase.

If I press Shift and type in those letters, that works as well. But if I back off and try typing in any numbers, it won't let me do that. So go ahead and say Next. We are going to, well, there are actually no symbols in this mask, so it doesn't really matter how we choose here. But go ahead and say Next anyway, and Finish. It inputs the mask down here at the bottom and now if I go back to my Datasheet View, again, we'll have to save the table. Now I will go ahead and try typing in my states again. It doesn't matter how I try typing them in, I can't get a lowercase letter here.

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.