Use an Input Mask to control data entry in an Access database. For example, you can force users to enters a phone number with an area code or an employee identification code with 2 letters followed by 3 numbers.
Below is a cheat sheet for Access input masks.
(000)-000-0000 will force the user to enter a phone number with an area code.
(999)000-0000! will force to user to enter a 7 digit phone number but is optional for the area code. The exclamation mark causes the input mask read the numbers from left to right.
LL-000 will force the user to enter 2 letters followed by 3 numbers.
>L<??????????????? will make the first letter capitalized and the following letters lower case.
Check out my Access keyboard shortcuts guide or other Access blog posts by clicking on the Access link on the right sidebar.
About Steve Chase
I want to help you work in your business, so you can work on your business.
I want to help you achieve your goals and help you have a successful small business journey.
I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business.
But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers.
My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant.
Please contact me on my new website at www.sequentiasolutions.com
Thanks for sharing. Just passed my MOS Access 2010 certification exam. Now completed full cycle of the MOS 2010: Word, PPT, Excel, Outlook and of course Access.
Congrats Madeline! Way to go.
Steve
Where can I find the option to make a data entry such as “040416” change to “04/04/2016”?
Hi Christina,
I am not aware of any software accepting that date format. Sorry.
http://answers.microsoft.com/en-us/office/forum/office_2007-excel/when-typing-in-a-date-in-a-cell-without/51149fb9-d067-4882-8180-159e3c129b88?auth=1
Steve
Hey,
This webpage really helped me study for my Final CAT High School Exam
Thank you Steve!
excellent
when I want to allow only letter not degits L des not work, it replace all letters with L
Not sure how to work around that. You can post your inquiry in the Microsoft Access community forums. https://support.office.com/en-us/article/Ask-the-Access-community-a466ebfb-6b1f-446e-b85c-33b0d07e2fbe
Hi Steve, I was assigned to create an input mask for a telephone number using only the last seven digits (excluding the area code), is there a way to edit the input mask so the parenthesis for the area code do not show up? Thanks!
When you create a custom input mask wizard, one of the options at the end is to keep or not keep the symbols. You could try the not keep symbols option.
how to enter ww-AAA-1111
I’m not sure what you mean? What will the user need to type each time?
Hi,
how the input mask will be look like for emails ??
Hi Laura,
Is Null Or ((Like “*?@?*.?*”) And (Not Like “*[ ,;]*”))
https://support.office.com/en-us/article/Control-data-entry-formats-with-input-masks-e125997a-7791-49e5-8672-4a47832de8da#foremail
Laura, my earlier comment would not go in the input mask because emails vary so much. Instead it is recommended to use data validation instead. https://support.office.com/en-us/article/Control-data-entry-formats-with-input-masks-e125997a-7791-49e5-8672-4a47832de8da#foremail
Hi Steve, I would like to enter numbers 00/00/00/00.
2numbers/2numbers/2numbers/2numbers.
Separation must be /
What would be the correct mask for this. Please.
not sure if this would work because I cannot try it.
00\/00\/00\/00 https://superuser.com/questions/439251/ms-access-force-input-mask-characters-in-field
I am trying to create a database for a library service. I need to format (I think) the input mask for the status as either “In Stock” or “Checked Out” ( I already have a validation rule setup, but that doesn’t force capitalization) . I am confused on how to do it because both phrases are a different length and have capital letters at different spots.
I would do a combo box instead. This will give the user a drop down box to enter the data. https://support.office.com/en-us/article/create-a-list-of-choices-by-using-a-list-box-or-combo-box-70abf4a9-0439-4885-9099-b9fa83517603
I want a code in which the first letter of both the parts of the name i.e.firstname and lastname appears in capital (uppercase)
HI, I would like to enter the value in the format AA19/2012 Can you help me with the mask for this please?
Trying to format the medium time as 8:42a or 8:42p in Access 2007. I’ve tried 99:00a/p 99:00a\p 00:00ap 0:00ap hh:nna/p h:nna/p. I either get an error message when I try it or it gives an unexpected/undesired result. Any ideas?
Sorry Pat, I’m not aware of a solution.
Hi Steve,
I am looking to set and input mask where the users can enter a number sequence but then MUST finish with a capital letter. For example “4545454T”
I know I could use “0000000>L< " but the issue is sometimes the number would have 7 digits, other times it can have 8 or 6
Cheers
Leanne
Hi Leanne,
You can try this code and move by space to the required number’s length:
!”( (“999999>L”) or (“9999999>L”) or (“99999999>L”))”
Ayman