Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Input Masks and Data Entry


Input masks are special group of characters that can be used in the InputMask Property of data fields to make data entry work easier in Microsoft Access.  You can use them on Forms too.

Keyed-in Text Value changes into upper case automatically or inserts slash (/) characters or other valid characters in Date Format separating Day, Month and Year segments or inserts hyphen (-) characters in Telephone Numbers separating Country Code, Area Code and Telephone Number and so on.

When large volume of information is fed into the System manually this kind of support in data entry process goes a long way in making the work easier to the User, besides maintaining/displaying the data in a standard format.

Let us look into an example. Assume that we have a Text Field for entering Telephone Numbers and the sample InputMask Property setting is given below:

Input Mask of Telephone Number

(###) ###-#######;0;_

When the field is active before entering any values into the field it will look like the display below:

(___) ___-____

The key strokes that you will make to key-in the telephone number is +914792416637 but the value will be automatically positioned in appropriate places guided by the Input mask as (+91) 479-2416637. You don't need to key-in the brackets, space or hyphen in between separating Country Code, Area Code and Telephone Number.

The Inputmask Property Value is expressed in three segments separated by semi-colon.

The first segment value is the Input mask itself: (###) ###-#######.

The second segment value is 0 or 1. If the value is 1 then the separation characters (brackets, space and hyphen) are stored with the data in the field as (+91) 479-2416637 (the field size must be big enough to store all the characters). If the value is 0 then the keyed-in data alone is stored in the field as +914792416637 and the Input Mask is used for displaying the value only.

The third segment value (the underscore character in our above example) is used for filling the empty positions with underscore characters showing the data entry field size.

When the Input mask character is # in all required character positions; you are allowed to enter Digits, Spaces, Plus or Minus symbols only into the field or you may leave the entire field empty.

Input mask character 9 works in a similar way but it will not allow the usage of Plus or Minus symbols into the data field. Input mask character 0 allows to enter the digits 0 to 9 only and cannot enter Plus or Minus symbols.

Input Mask Date Field.

Input Mask Example2 (Date Field Input Mask): 99/99/0000;0;_

Sample Data Entry: _1/_1/1984 or 01/01/1984

Date value changes into: 01/01/1984

In the Day and Month positions you are allowed to enter a Space but in the Year position all four digits must be entered because the 0 input mask will not allow Spaces and cannot leave that area empty. But, you can leave the Date Field totally empty.

Input Mask of Text Field.

Input Mask Example3 (Text Field): >CCCCCCCCCCCCCCC;0;_

Allowed to enter any Character or Space or you can leave the data field blank. The Text Value entered will be converted into Upper Case and you don't need to bother about the CAPS-LOCK settings.

If you use the word Password as Input Mask Value then whatever data you enter into the field will appear as a series of * characters and the actual value entered is not shown.

The list of Input Mask characters and their usage descriptions are given below.

Character    Description

0            Digit (0 to 9, entry required, plus [+] and minus [-] signs not allowed).

9            Digit or space (entry not required, plus and minus signs not allowed).

#            Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).

L            Letter (A to Z, entry required).

?            Letter (A to Z, entry optional).

A            Letter or digit (entry required).

a            Letter or digit (entry optional).

&            Any character or a space (entry required).

C            Any character or a space (entry optional).

. , : ; - /  Decimal placeholder and thousand, date, and time separators (separator: A character that separates units of text or numbers.). (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).

<            Causes all characters to be converted to lowercase.

>            Causes all characters to be converted to uppercase.

!            Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.

\            Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).

Password     Displays * in all keyed character positions.

You can use the above characters in a mixed form to get the output the way you want.

For example, the Input mask >C<CCCCCCCCCCCCCC;0;_ will change the first character into Upper Case and the rest of the Text into small letters and accepts up to 15 characters or less in the field.

Technorati Tags:


  1. Good post and this post helped me alot in my college assignement. Say thank you you as your information.

  2. I need to know what vaule to enter for a name like McDonald. An autocap with the additional option to use Caps if needed but otherwise text remains in lowercase. Is this possible?

  3. Input Masks cannot be conditional but with manual intervention (like button clicks or checkbox clicks etc.) it is possible with VBA.

    For McDonald like names set the Input Mask to ">L<L>L<????????????"

    For first letter cap and remaining characters in lower case: ">C<??????????????"
    For all upper case letters you can remove the < symbol.

    If the above Input Masks strings are required on the same textbox at your will then create two Command Buttons (cmd1 and cmd2) and add two Click Event procedures as given below:

    Private Sub cmd1_Click()
    Me.txtDisplay.InputMask = ">L<L>L<????????????"
    End Sub

    Private Sub cmd2_Click()
    Me.txtDisplay.InputMask = ">C<??????????????"
    End Sub

    The Input Mask string will be set to the txtDisplay Text Box depending on which command button you clicked before entering values into it. Even after entering value if you click the command button you can see the text changes.


Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts