Access Input Masks
Welcome to November's Hot Top from Black Swan Training Solutions. This month we will look at Input Masks in Access.
INPUT MASKS
Users of Access have probably used Input masks even if you haven't realised what they are called. The most common Input Mask is used in date fields to make sure you enter the date in the correct format. If you have moved to a date field and it displays something like this __/__/__ it is the Input Mask specifying that you must enter the date in three pairs of numbers ie. dd/mm/yy.
An Input Mask controls what the user can type into a field and helps in three ways:
1. The user can see roughly what they should be typing in,
2. typo's and other mistakes are reduced so that the data is more accurate,
3. thus, data entry is quicker and more reliable.
The database designer can create Input Masks whenever there is a predictable pattern for the data, and the Input Mask enforces that pattern. Dates, postcodes, car registrations are all examples of this.
You can create Input Masks in tables or forms. We recommend you create them in your tables, so that any forms you create will benefit from the Input Mask automatically.
CREATING AN INPUT MASK IN AN ACCESS TABLE
1. Open the table in design view.
2. Select the field you wish to have the input mask, so that you can force users to type the right things.
3. In the Field Properties list in the bottom half of the window, select Input Mask.
4. Type in the code for your Input Mask (as below).
The Input Mask is now set. You should test it with some unusual examples of data that you might get.
INPUT MASK CODES
The code for an Input Mask controls what the user can type into the field and how much they can type. The basic codes are:
0 (zero) where the user must type in a number
L where the user must type in a letter
Therefore the code for a national insurance number would be LL000000L. This code doesn't have the spaces you normally see in an NI number, however. So you can type in LL 00 00 00 L. Access will then correct that to LL\ 00\ 00\ 00\ L so that the spaces are always put in. (See Note 1)
The full list of codes is:
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. (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).
The example of using an Input Mask on a postcode field is interesting because postcodes are not as consistent as they seem at first glance. They range from B1 3TU, RG21 7PW to WC1A 4WW. So make sure you consider all possible data before putting in your input mask. If the user has some data that doesn't fit the Input Mask they won't be able to enter it correctly.
EXAMPLE INPUT MASKS
Post code: >LAaa 0LL
Date (dd/mm/yy): 00/00/00
Date (dd-mmm-yyyy): 00-LLL-0000
Time: 00:00
Monetary values (less than £10,000): £9,999.99
Telephone number: 000 000 00000 (all UK phone numbers are 11 digits but have the spaces in different places)
NB: Because of personalised number-plates an Input Mask is probably inappropriate for most car registration fields.
CREATING INPUT MASKS ON A FORM
If you have created an Input Mask in the table, any forms you subsequently create will copy the Input Mask into the appropriate field on the form.
If you want to modify or add an Input Mask do the following:
1. open the form in design view;
2. right-click the field in question and give the command Properties;
3. select the Data tab and find the Input Mask property;
4. enter the Input Mask code in the same way as you would in the Table design.
Remember, Input Masks are a very useful way of making data entry more user-friendly and accurate. When creating them, make sure
Note 1: Another way of doing this would be LL" "00" "00" "00" "L, where the " " is specifying a deliberate (literal) space. The difference between " " and \ is that the \ saves a bit of space because it is only one character not two. However, the \ denotes that only the immediately following character is literal where " " must be used if there is more that one character. For example, "XL"000 would mean the user types a three digit number that will always be preceded by XL, whereas \XL000 only the X would be understood to be a literal character and the L would be a code for any letter to be typed in.