|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Input Mask By Richard Rost 
5 years ago
Use an Input Mask for Phone Numbers, ZIP Codes In this video, I will teach you how to use an Input Mask in your Microsoft Access tables and forms. We will create input masks for telephone numbers, ZIP codes, dates, and more. Mckenzie from Chandler, Arizona (A Gold Member) asks: The phone number field in my database is a mess. Some people are typing in dashes, others parentheses, others with nothing at all. I've got: 239-555-1212 I recently wanted to make a report showing customers by area code, and I couldn't do it because my data isn't uniform. How can I force my users to use ONE format for entering phone numbers? MembersMembers will learn how to dynamically change the input mask based on another field. For example, for US & Mexico, use "00000" for the ZIP code. For Canada use "L0L 0L0" for the Postal Code. Same field, different input masks. We'll use a little VBA to swap them on the fly.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
AddendumIn the Extended Cut video, I mention that if you use the VBA code in your forms to change the input mask that you would also need to put that code in your reports as well. That's not necessarily true. If you store the input mask characters in the table field with the ;0 parameter, you won't need any additional changes in the report. Just display what's in the table without any formatting. Input Mask CodesUse the following codes when creating your input masks, as explained in the video. Remember these are different from the codes you use in the Format property and function.
Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, input mask, Control data entry formats with input masks, Create an Input Mask, setting input masks, Create Input Masks in Access, What is the Input Mask Wizard, Defining Input Masks, How To Create An Input Mask In Access, How to Set an Input Mask, input mask access example, input mask access phone number, input mask codes, input mask characters, input mask zip code
IntroIn this video, I will show you how to use an input mask in Microsoft Access to ensure consistent data entry, focusing on formatting fields such as phone numbers and zip codes. We will cover how to apply input masks with the wizard, explain the meaning of various input mask characters, discuss storing data with or without formatting symbols, and look at practical examples for both US and Canadian formats. I will also show you how input masks interact with forms and queries, and demonstrate potential issues when using input masks with date fields.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.In today's video, I'm going to show you how to use an input mask in Microsoft Access. Today's question comes from Mackenzie in Chandler, Arizona, one of my Gold members. Mackenzie says, "The phone number field in my database is a mess. Some people are typing in dashes, others parentheses, others nothing at all. So I've got 239-555-1212. I've got parentheses, and I've got no formatting at all. I recently wanted to make a report showing customers by area code and I couldn't do it because my data isn't uniform. Normally, all you have to do is pull off that left three characters and you can get everyone's area code. But if you've got parentheses sitting there, it's impossible. How can I force my users to use one format for entering phone numbers?" Well, Mackenzie, to do this we can use something called an input mask, which forces users to type data in a particular format. Now, Mackenzie, the first thing you are going to want to do is go watch my video on the Replace function. I'll show you how to use the Replace function with an update query to get rid of all of that formatting. Right now, you've got those dashes and parentheses and whatever stored in your table. We want to get rid of all of that and just have the digits, just the numbers themselves. So, go watch this video. I'll put a link to it down in the links section in the description down below the video. Click on that, watch that video, and then come back here. Now that you've gotten all of the formatting out of your phone number field, let's take a look at the TechHelp free template. This is a free download up on my website. You can grab a copy of this if you want to. You'll find a link down below in the links section. Let's take a look at the customer table. We've got a phone number field right there, and I've got some phone numbers in here. I'm using all US-Canada standard: three-digit area code, three-digit prefix, four-digit suffix. Obviously, you're going to want to use the formatting for your country, and if you do a lot of international business with different countries that have different formats, you may want to consider whether or not you want to use input masks. But if most of your customers are in the US and Canada, feel free to follow these instructions. In fact, just as a side note, I do have another video, another TechHelp video, a free video, on input masks of varying lengths. Someone, a while back, said in their country, they allow both seven-digit numbers and eight-digit numbers. So I have a separate video on that if you want to be able to see how to do multiple variable-length input masks. But for the rest of this video, we'll assume everyone's in the US and Canada. Now, let's go into design view and let's come down to the phone number field where I am right there. Here's the input mask property. Click in there. Now you can type in an input mask yourself, or there's a handy little wizard over here. Click on this little dot dot dot button; that will bring up the input mask wizard. This is a good wizard. I do not mind the input mask wizard. There are good wizards and there are bad wizards. Are you a good witch or a bad witch? Well, this is a good wizard. I like this one. Some of the wizards, like the form wizard, I do not like. But this one's not bad. It just says, "Here's a list of popular input mask formats." You can take a look down this list. Different dates and stuff. There's a password one, social security number, zip codes, phone numbers right there. You can try it by clicking down here; you can see you get the little placeholders in place right there. Let's just go with this one to start. Next. Here's what it's going to look like. We'll talk more about these digits in a minute. What do you want for a placeholder character? That's the character that gets displayed while people are typing. The underscore is fine. You can click down here and try it again: 239-555-1212. Looks good. Next. Now, do you want to store the data with those characters in the field or just the numbers? If most of your customers are US/Canada, all the same format, I suggest storing it without the symbols. Hit next and then finish. So there's our input mask now. It gets a little more complicated. I'll zoom in so you can see that better: Shift + F2 to zoom in. It looks like this, and I'll explain what all this means in just a minute. Just hit OK. Save it. Close it. Let's open up the customer table again and come over here. Now you can see all the numbers are pretty formatted with those input masks. If you tab into it and start typing 239-555-1212, you are forced to type in that format exactly. If you do a lot of local business and someone just types in 555-1212 and presses tab, it is going to yell at them. It is going to make them have to type those in. Now, you can use blanks for the area code by just hitting space three times and then 555-1212, although I do not like that. Even if you are like a pizza place and your customers are all local, I really hate doing that because, again, now you do not have a consistent format with all your digits. So let's talk about those characters for a second. Let's go back into here. Let's come back down and find that phone number field right there. I'm going to zoom in again so you can see it. You notice there are nines and zeros and a bunch of other characters. Don't pay any attention to those other characters now. Let's talk about the nines and the zeros. Nine means you may put a character here or not. You can put a space there if you do not want a character there. Zero means you have to put a character there. It forces you to put a digit there. If you are only forcing this to be a seven-digit number, you could leave a blank area code. If you want to force that to be typed in there, put three zeros there: 000. Hit OK. Save it. Now if I go back in here... Now it does not revalidate all the stuff that's in the table already. You have to make sure you have good data in there to begin with. The input mask only applies to new stuff. If I try to type again 555-1212, it will let me. 239-555-1212. There we go. Let's talk about all those other characters that are possibly in there. A zero means you must enter a digit. Nine means you may enter a digit. The big difference there is whether you have to have it or may have it. A pound sign means you may enter a digit, space, plus, or minus sign; blank space if it's skipped. Then we get letters. Capital L means you must enter a letter. Question mark means you may enter a letter. Capital A must enter a letter or a digit. Lowercase a may enter a letter or a digit. Then we have ampersand, which means must enter a character or a space. Capital C means you may enter characters or spaces. I cover all the differences and nuances of these in my full courses. I'll go over a little bit more in the extended cut for the members too. If you want a password, just type in the word "password" in the input mask and it will echo stars. It will store the actual data in the table, but you'll only see stars when you go to view it. Then you've got all the decimal, thousands, date, and time placeholders that are based on your regional settings. For example, some countries use a decimal point, some use a comma for the decimal placeholder. There are also colons, semicolons, and dashes and slashes for dates. A backslash means the character immediately following the backslash is literally displayed. That means, for example, in this one here, you can see there's a backslash right in front of that parenthesis. That means that parenthesis is literally displayed in the input mask. Just after this one, that dash is literally displayed. You'll see here there are quotes. Anything inside quotes is also literally displayed. That's the next one. Characters enclosed in quotes are literally displayed. Then you've got greater than and less than, which convert to uppercase or lowercase. The exclamation point makes characters fill from left to right instead of going right to left. For example, you go left to right, display that literally, three characters you must enter, display that literally, three more characters you must enter, display that literally, four more characters you must enter. After the semicolon, there are a couple of optional parameters. Don't worry about these right now. Let's hit OK. Let's close that. Now one thing to notice is that if you change the input mask in your table and you open up your form, it does not put the input mask in there. You have a couple of options. You can either manually put the input mask in here. If you look at the design view, go into the phone number's properties, you can see there's an input mask right there. You can copy it and paste it over here. Or you could bring the field in again from the add existing fields. Grab phone, drop it in here again. Now you'll notice that it has the input mask. Or there's another option. When you change the input mask, let's say that you change the input mask in here. Let's change this just slightly. Let's make this 000-AAA-AAA. So I want three must-have digits, followed by three must-have letters or numbers. You could do phone numbers like 1-800-FLOWERS and put letters in the phone number. Sometimes that's handy. Hit Tab. Now, as soon as I hit Tab, notice this thing pops up. It says, "Update the input mask everywhere phone is used." In other words, Access knows that you've used this phone number field on other queries, forms, and reports, and if you click on this, it will actually propagate through everywhere. It is a really nice feature. It will copy that input mask everywhere you have used that field. I'm going to click on that. It found CustomerContactF and CustomerF have that field in them. Save changes. Yes. If I come into here, you can see there's my input mask. I can also come in here and go F-L-O-W-R-S. That's kind of handy. That's kind of cool. Now you could do 1-800-DRU-ID-YA. Spaceballs fans out there. Let's check out another one. Let's go back to CustomerT. Let's do the zip code. What do you want for your input mask for your zip code? Well, let's see what the wizard gives us. Dot, dot, dot. There's a zip code. Now, the zip code for the wizard is going to include the zip plus four. Personally, I like to put the zip plus four in a second field. If you're doing just US zip codes, I like to put the five-digit zip code in one field and the plus four in another field. Honestly, the only reason to use zip plus four is if you're doing mass mailings, if you are doing tons of them, and you're getting a bulk discount from the post office. Otherwise, I never bother with zip plus fours. Your mail is just going to get there just fine without it. But the post office likes it for sorting. If you've got a bulk mail permit, you have to have plus fours on there. Next. That's what it's going to give you: five must-have characters followed by four optional characters. Next, with or without the symbols, again, that's up to you. Next, and then finish. There it is. Save it. Close it. Come back in here, and there they are. You can optionally put that in or not. Now, one thing I want to show you real quick is with this input mask, we are not storing these dashes in the field itself. If you look at this data without the input mask, you'll see just the numbers or the numbers and letters. You can see this if you do a query real quick. Create query design, bring in the customer table, then bring in the phone number field. Where's phone number field? Now, if you run it, it will display with the input mask. But let's do a simple function where I take the middle, let's say the middle three characters out of that prefix. So I'm going to say X = Mid([Phone], 4, 3). I want the fourth character, three characters long, and then run it. Now, you'd think that the fourth character would start there, that little dash, but no, that character doesn't exist in the field. The fourth character is actually the D, which, if you count it, is the fifth character in. Just be aware of that if you are using an input mask. Those input mask characters do not actually exist in the field unless you store them in the field. That's one of those options on the wizard, and that's what one of those optional parameters are. If you look at it, let's use one that the wizard puts in, like the phone number field. Finish. See? It puts those little optional parameters at the end of it. This first optional parameter: if you want those characters to be stored in the field, put a 0 there. If not, leave it blank. I know it's weird. I didn't make this up. I'm just reporting to you how Microsoft set it up. If you want those parentheses and dashes to actually be saved in the field, put a 0 there. Or say yes in the wizard if you're going through the wizard. This little guy here, after the next semicolon, is whatever you want the placeholder character to be. So, put a dash there if you want to change it from an underscore to a dash. Save it. Do it. Now when you come over here, you'll see you get the dash. If I go 555c... OK. If you are doing all Canadian zip codes - or postal codes in Canada, first of all - but if most of your customers are Canadians, you're going to go greater than L0L backslash space to put a space character there, 0L 0. I said O before. It's 0. L0L 0L 0. If you look at the chart, this is convert all the letters to capital, letter, number, letter, a literal space, number, letter, number. That's the Canadian format. Now if I save it and close it and go back in here, you'll see that all the American ones don't really pick that up, but if I come over here and put a zip code in now, that's going to make me do a Canadian zip code. Not appropriate. I have to put it in the right way. There. See? If you want to force those placeholders to display, make sure you come in here in zip code and go semicolon, semicolon, and then underscore. Save it. Close it. Back in here. There. Now, what if you have a mixture of American and Canadian customers? What if you do a lot of business in the US and Canada? You want to switch that input mask? That's covered in the extended cut. That requires some VB programming, but you can see here I can switch it: if the country says Canada, change the input mask to that; otherwise, if it's US, change it to that. That will be in the extended cut for the members. I'm not finished yet, I've got one more example. Let's talk about dates real quick. I personally don't like input masks for dates. I really do not like them. Let's just go into the form for a second here. See this "Customer Since" date? If you put an input mask on for the date, you lose the date picker control. Why? I don't know. That's just something Microsoft did. If you put an input mask on here, the date picker control goes away. No matter what I put in here, if I go to "Customer Since," input mask, if I just do something simple like the short date and then finish, save it, come back in, and click: it's gone. Why? I don't know. I'll put that on my list of complaints for Microsoft. Put that back. I don't like how it spreads it out like that. I'm just not a fan. The one thing that I do like with input masks is if you want to have a format where you've got the day and then the three-character month, like OCT for October, and then the year. If you like that as a date format - that's my preferred one, I love that format - and for computers, I like year, month, day. That's how dates should be: yyyy-mm-dd, and then everyone's on the same format. The fact that the US does it one way and Britain does it another, no, it's silly. So let me show you one that I do like for dates. I'm going to go into the input mask here for the date, hit the dot, dot, dot, let's pick the medium date - this guy right there, 27 September 69. Next, I'm going to modify it just slightly. I'm going to make this a four-digit year. Next. It's not giving me the option that I want. I want that option where it saves the values in the table, but I'm going to finish and add that myself, because you need that for this to work. You have to have that little character at the end for this to work. If the formatting isn't saved with this one, it doesn't work right. Save it, close it, open it up again. Now it's not displaying that way, but watch this. If I start up here and tab into it, watch this: 02-October-2000. There we go. Now it converts it to 10/2/2000. Why is that? Well, you have to remember, the input mask and the format are two different things, and you need both of them for this to work right. Come back into here. Notice the format is not set; the format right here is blank. We're going to change this to dd-mmm-yyyy. Two-digit day, three-digit month, four-digit year. Format codes and input mask codes work differently. Now if you come back in here, you'll see it's displayed that way too. You can come in here and say 23-October-1972. That's my birthday. Go to the next one, display that way. Click in here, change this to March, go on. That gets stored as a valid date value. Your input mask and your format display it properly, and everything works great. If you want to learn a lot more about input masks, I cover input masks in these three classes: Access Beginner 3, Beginner 6, and Expert Level 4. I cover different things at different levels. I like to teach with a breadth-first teaching style where I give you a little bit of this, then a little bit of that, and I go kind of how you should learn it. Most books and a lot of courses I've taken at colleges, for example, teach depth-first. It's like, "We're going to learn everything about tables today. Everything about queries next." That's depth-first. You're going to learn everything about input masks in one lesson. I don't teach that way. I teach a little bit of queries, a little bit of forms, then we go into reports a little bit, then we come back to tables. As we get more complicated, I get into more complex stuff. So if you want to learn all the rest of the input masks, these are the three classes for you. For members, stick around for the extended cut. I'm going to show you how to do that thing where we switch the input mask for the zip code or postal code based on the country that the person is in. Remember, Silver members and up get access to all of my extended cut videos. There are over 200 right now. You have lots of other perks and free stuff on my website, so sign up today. Hope you learned something, and I hope you had a great year, 2021. This is probably the last video I'm doing for this year, so we'll see you in 2022. How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you've finished the Beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. It will be shown in each video as long as you are a sponsor. You'll get a shout-out in the video, and a link to your website or product in the text below the video and on my website. Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. If you enjoyed this video, please give me a thumbs up and post any comments you may have. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free, and click on the bell icon to select "All" to receive notifications when new videos are posted. Make sure you click the "Show More" link down below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a video, click on the link to join my mailing list. Even if you do not want to become a member, feel free to donate to my tip jar. Your patronage is greatly appreciated and will help keep these free videos coming. I have puppies to feed. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access. It's over four hours long, and I just updated it for 2021. You can find it on my website or on my YouTube channel. I'll include a link below that you can click on. Also, if you like Level 1, Level 2 is just one dollar. Yep, that's all: one dollar. It's free for all members of my YouTube channel at any level, even Supporters. Want to have your question answered in a video just like this one? Visit my TechHelp page on my website. You can send me your question there. While you're on my site, feel free to stop by the Access Forum. Lots of good conversations are happening there. Be sure to follow my blog, find me on Twitter, and of course YouTube. Once again, my name is Richard Rost. Thank you for watching this TechHelp video brought to you by AccessLearningZone.com. I hope you enjoyed this video and that you learned something today. I'll see you again soon. QuizQ1. What is the main purpose of using an input mask in Microsoft Access?A. To format data when printing reports B. To force users to enter data in a specific format C. To automatically sort data alphabetically D. To change how the data is stored in a table Q2. Why is having inconsistent formatting in a phone number field problematic for finding area codes? A. Access cannot search for area codes at all B. Formatting symbols like dashes and parentheses make extracting area codes difficult C. The area code is always at a random position in the field D. It makes the field incompatible with reports Q3. Before applying an input mask to a phone number field that already contains data, what should you do? A. Manually retype all the phone numbers B. Remove all formatting using an update query with the Replace function C. Sort the data alphabetically D. Add a new field to store formatted numbers Q4. What is the primary function of the input mask wizard in Access? A. To create new tables automatically B. To provide preset formats and help create input masks for fields C. To apply filters to database queries D. To sort data by input mask format Q5. In an input mask, what does '0' mean? A. The character is optional B. Only letters can be entered C. That position must contain a digit D. Any character can be entered Q6. What does '9' signify in an Access input mask? A. The character is required B. A letter is required C. The digit is optional D. Only letters or digits are allowed Q7. If you want to force users to enter a three-digit area code for US/Canada phone numbers, what should you use at the beginning of the input mask? A. 999 B. AAA C. 000 D. 123 Q8. What is the effect of selecting "store the data without the symbols" in the input mask wizard? A. The symbols are displayed and stored in the database B. Only the digits are stored in the field, symbols are not saved C. The symbols are changed to spaces in the field D. The data is encrypted Q9. What happens if you change the input mask in a table, but the field is already used on other forms or queries? A. The input mask changes everywhere automatically B. You have to manually update it everywhere C. Access offers to update the input mask everywhere the field is used D. All existing data is deleted Q10. What does the backslash (\\) in an input mask do? A. Makes the following character uppercase B. Converts numbers to letters C. Displays the following character literally in the field D. Ends the input mask Q11. In input masks, what does a capital 'L' represent? A. Must be a lowercase letter B. May be a letter or digit C. Must be a letter (A-Z) D. May be any character Q12. Should you use an input mask on date fields if you want to retain the use of the date picker control? A. Yes, input masks enhance the date picker B. No, input masks remove the date picker control C. Only if the input mask uses a four-digit year D. Only for long date formats Q13. Why does Richard Rost prefer displaying dates as yyyy-mm-dd in his databases? A. It matches US regional settings B. It matches the default input mask in Access C. It's a universally clear format for computers and humans D. It displays the month as a word Q14. If you want to apply a Canadian postal code input mask that forces capitalization and the correct pattern, which input mask should you use? A. ">L0L 0L0" B. "000-000" C. "99999" D. "LLLLLL" Q15. What is the difference between 'input mask' and 'format' properties in Access? A. Input mask is for display only, format is for entry only B. Both are the same and interchangeable C. Input mask controls data entry structure; format controls how data is displayed D. Format is used in queries, input mask in reports Q16. What happens if you want to use both US and Canadian formats for postal codes in the same field? A. Access will automatically detect the country and switch formats B. You must manually switch the input mask with VBA or custom programming C. You cannot store both formats in one field D. Only the US format is supported Q17. When storing a phone number with both digits and letters (like 1-800-FLOWERS), which input mask segment should you use after the initial digits? A. 999-9999 B. LLL-LLL C. AAA-AAA D. XXX-XXX Q18. Why might you want to store the ZIP+4 extension (ZIP plus four) in a separate field? A. It is required by Access B. For mass mailings, it makes sorting easier C. To encrypt sensitive zip codes D. Only the ZIP code is ever needed Q19. What is the use of the optional parameters after the semicolons in an input mask string? A. To set text alignment B. To specify whether to store literals and choose the placeholder character C. To encrypt the field D. To define the maximum field length Q20. Where can you learn more about input masks in Richard Rost's courses? A. Only in Access Beginner 1 B. In Access Beginner 3, Beginner 6, and Expert Level 4 C. In Access Advanced 2 D. Only in YouTube playlists Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-C; 7-C; 8-B; 9-C; 10-C; 11-C; 12-B; 13-C; 14-A; 15-C; 16-B; 17-C; 18-B; 19-B; 20-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to use input masks in Microsoft Access. This question comes from Mackenzie, who is dealing with inconsistent formatting in a phone number field in her database. Her issue is that users are inputting phone numbers in various formats, including the use of dashes, parentheses, and no formatting at all. This makes it difficult to extract area codes for reporting because the data isn't uniform.To address this, I recommend first cleaning up the data in your phone number field. The first step involves using the Replace function in an update query to strip out any unnecessary characters, so you are left with just the digits. I have a separate video that walks through this process, and you can find the link on my website. Once your phone number data consists of only digits, you can then move on to adding an input mask. For this demonstration, I'll be using the free TechHelp template available for download from my site. In the customer table, you'll see the phone number field meant for the standard US and Canada format: three-digit area code, three-digit prefix, and a four-digit suffix. If your database handles international numbers, keep in mind that input masks might not be the best solution unless you can define a consistent format. If you need input masks of varying lengths, there's another video on that topic. For today, though, we'll stick to the US and Canada format. Switch to design view, and find the input mask property for your phone number field. You can enter your own mask or use the built-in Input Mask Wizard, which offers several preset formats, including phone numbers, social security numbers, ZIP codes, and more. Selecting the phone format in the wizard will set up the correct mask for standard US and Canadian numbers. During setup, you can choose a placeholder character (like an underscore) and decide whether you want to store the formatting characters (like parentheses and dashes) in the table or just the raw numbers. I suggest storing only the numbers for consistency, especially if your data uses a uniform format. After applying the input mask, when you open the customer table and enter a new phone number, Access forces you to follow the specific format. If someone tries entering only the local part of a phone number, leaving out the area code, Access will require them to fill out the area code as well. While Access allows space characters if the mask uses nines (making those digits optional), it's best to make all parts required by changing the mask to use zeros. This ensures that every entry is consistent. Input mask characters have specific functions: zero requires a digit, nine allows a digit or space, pound allows a digit, space, plus or minus sign, capital L enforces a letter, question mark allows a letter, capital A requires a letter or digit, lowercase a allows a letter or digit, ampersand requires a character or space, and capital C allows characters or spaces. The wizard also offers different regional formatting for dates, decimals, and so on, depending on your country's settings. Literal characters can be included with a backslash or by enclosing them in quotes. Greater than and less than symbols switch entries to uppercase or lowercase, and exclamation points change the fill direction for the mask. After the semicolon in the mask property, you can specify additional parameters, like whether to store placeholder characters in the field. If you change the mask in the table, Access does not automatically apply that change to forms or queries. You have to manually update the input mask property for those controls or use the feature that lets you propagate changes across all forms and reports where that field is used. Access will prompt you to confirm this update. The input mask can be adapted for things like vanity phone numbers (e.g., 1-800-FLOWERS) by mixing required digits and letters in the mask. Moving to ZIP codes, the wizard includes support for ZIP+4. My personal recommendation is to use a separate field for the extra four digits, unless you're doing bulk mailings and require the full ZIP+4 format. For most cases, the five-digit ZIP code suffices. It's important to realize that unless you specify otherwise in the mask properties, the dashes and parentheses shown as formatting are not stored in the field. So if you write queries to extract data, those characters will not be present in the stored values. This can affect how you write expressions that rely on character positions. For Canadian postal codes, the mask should be set to force the correct pattern: alternating letter, digit, letter, space, digit, letter, digit. A greater-than sign at the start can automatically convert letters to uppercase. If your database serves both US and Canadian addresses and you want to switch input masks dynamically based on the country, that's a more advanced technique involving VBA, and I cover it in the extended cut for members. As for dates, I generally do not recommend using input masks, because applying one causes the date picker to disappear from forms. This seems to be a quirk of Access, and I wish Microsoft would change it. For situations where you want a specific date format (for example, day-month-year in three-letter abbreviations), you can build an appropriate input mask and set the format property as well. This gives you the display and validation you want. If you're interested in learning more about input masks, I cover them at multiple points in my courses: specifically in Access Beginner 3, Beginner 6, and Expert Level 4. My approach is to introduce topics gradually so you see them in different contexts rather than covering everything in one lesson. For those with membership, the extended cut demonstrates how to automatically change the ZIP or postal code mask based on a customer's country. Silver members and higher get access to these extended videos and additional perks. To sum up, input masks are a great way to standardize data entry in Access for fields like phone numbers, postal codes, and more. By using them, you can ensure that your data remains consistent and reliable for reporting and querying. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListUsing input masks to standardize phone number formattingCleaning existing phone numbers with update queries Opening and editing table input mask properties Using the Input Mask Wizard in Access Selecting and customizing input mask formats Choosing to store symbols with or without data Explanation of input mask mask characters (0, 9, #, L, ?, A, a, &, C) How literal characters are displayed in input masks Using quotes and backslashes for literal characters Applying greater than, less than, exclamation to masks Propagating input mask changes to forms and reports Allowing letters in phone numbers (vanity numbers) Setting up input masks for US zip codes Storing and retrieving data with or without input mask symbols Setting up input masks for Canadian postal codes Adjusting placeholder characters in input masks Input mask considerations for date fields Combining input mask and format for custom date display How input mask affects date picker control Setting custom date formats (dd-mmm-yyyy) in Access |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access input mask, Control data entry formats with input masks, Create an Input Mask, setting input masks, Create Input Masks in Access, What is the Input Mask Wizard, Defining Input Masks, How To Create An Input Mask In Access, How to Set an Inp PermaLink Input Mask in Microsoft Access |
