Restricting Data Entry


The Format property controls the appearance of data, but it doesn t actually force people to enter the correct kind of data. You can use an input mask, or character pattern, which you specify in much the same way as the Format property, to restrict data entry to the desired information.

start sidebar
What is null?

In Access, null indicates a missing or unknown value in a text, memo, or hyperlink field, and it typically means that no value has been entered in the field. Null is different from a zero-length string, which is an entry that contains no characters . You can enter a zero-length string by typing two quotation marks with nothing between them ( ).

end sidebar
 

Specifying an Input Mask

You specify an input mask to determine how data looks as it is typed into a form or table and, optionally , how it is stored in the table. Here is a list of the characters you can use in the Input-Mask property field, together with what they mean to Access:

Character

Access s Action

Requires any digit (0 through 9); + and “ not allowed.

9

Allows any digit or space.

#

Allows any or no digit, +, -, or space.

L

Requires a letter.

?

Allows any or no letter.

A

Requires a letter or digit.

a

Allows any or no letter or digit.

&

Requires a character or space.

C

Allows any or no character or space.

. , : ; - /

Placeholders: actual character determined by regional settings.

<

Converts all following letters to lowercase.

>

Converts all following letters to uppercase.

\

Inserts the following character as entered.

!

Right-aligns the entry.

Password

Stores characters as typed, but displays an asterisk (*).

In the next few procedures, you will use these placeholder characters to create input masks for several of the fields in the Employees table.

Using the Input Mask Wizard

Because you used the Table Wizard to set up the Employees table, Access has already specified input masks for some fields. Let s take a look at a few of these input masks and make any necessary changes. Follow these steps:

  1. Display the Employees table in Design view.

  2. Click anywhere in the SocialSecurityNumber row in the field grid.

  3. In the Field Properties area at the bottom of the window, click the Input Mask box.

    An insertion point appears in the box, and a Build button is displayed at its right end.

  4. Click the Build button.

  5. If the wizard is not yet installed, click Yes to install it. Insert your installation CD-ROM and click OK if prompted, then remove the CD-ROM when the installation completes.

    The Input Mask Wizard displays its first page, shown in this graphic:

    click to expand

    This dialog box offers a variety of common masks. You can scroll through them to get an idea of what is available.

  6. Click Social Security Number , and click Next .

    In the Input Mask text box, the 0s are placeholders for digits you must enter and the hyphens are literal characters that Access will enter for you.

  7. Click Next .

    You are asked whether you want to store the data with or without the literal characters.

  8. Click the With the symbols in the mask option, and click Finish .

    When you move away from the Input Mask property, Access automatically places a backslash ( \ ) in front of the two hyphens so that they will be treated as literal characters.

  9. Click the PostalCode field, click the Input Mask box, and then click its Build button.

  10. Click Zip Code , and click Next .

    In the Input Mask box, you again see the 0s that are placeholders for digits you must enter. In this field, the 9s are placeholders for optional digits ”in this case, a ZIP+4 Code extension. Access will enter the hyphen whether or not you enter the extension.

  11. Click Next .

  12. Retain the default option of Without the symbols in the mask , and click Finish .

    The mask is in serted in the Input Mask box.

  13. Set the Field Size property to 9 to match the number of digits in the ZIP+4 input mask.

    Now Access will accept from five up to nine digits in this field.

start sidebar
The Input Mask List

If none of the predefined masks matches a data entry requirement that you expect to use repeatedly, you can click Edit List in the wizard to modify one of the existing masks or create your own mask templates.

end sidebar
 

Deleting an Input Mask

You have to be careful when placing restrictions on user input, or you might prevent the user from doing something perfectly legitimate . For example, you can specify an input mask for text that ensures that the first letter of a person s last name is capitalized and all other letters are lowercase. But then what happens if you have an employee whose name doesn t fit those parameters? (Some last names begin with a lowercase letter, and some have internal capital letters ”McDonald, for example.) If you decide that you don t want to associate a certain character pattern with a data field, you can remove an input mask at any time.

Let s remove the input mask from the DateHired field. Follow these steps:

  1. Click anywhere in the DateHired field to display its properties.

  2. In the Field Properties area at the bottom of the window, click the Input Mask box.

  3. Select the entry in the Input Mask box, and press Delete .

start sidebar
Data integrity

Always stop and think before you change the field sizes and input masks for your data. Such changes can result in data losses that will seriously affect the usability of some fields. For example, if you have entered area codes in the Phone field, eliminating the area code from the input mask might result in the last three digits of the employee s phone numbers being chopped off.

end sidebar
 

Testing an Input Mask

It is always a good idea to test changes as you make them. Follow these steps to enter a couple of new employees and test your input masks:

  1. Click the View button on the toolbar to switch to Datasheet view.

  2. If Access asks if you want to save the table, click Yes . Then, if you are warned you might lose data, click Yes again.

  3. Decrease the widths of the fields to that you can see all the employee data at once, as shown in this graphic:

    click to expand

    The table reflects all the visual changes you made to its structure. The Emp# field displays three digits, and Lakewood and 98403 have been entered by default in the blank record at the bottom of the field. Let s add more employees to try everything out.

  4. Click the Emp# field of the blank record, type 36 , and press Enter .

    Access enters 036 in the field.

  5. Type the information in the table on the following page in the indicated fields, pressing Enter three times to skip the City and Zip fields.

    Field

    Record 7

    Record 8

    Emp#

     

    37

    SS#

    999214972

    999829921

    First Name

    Jonathan

    Jefrey (use this spelling)

    Last Name

    Bray

    Davis

    Address

    5941 Hilltop Road

    1171 Simon Heights

    Phone#

    5550180

    5559297

    First Day

    06/17/96

    06/12/97

    As you enter the field values, notice the effect of the field properties specified as part of the table s structure.

  6. Press Enter to complete and save the eighth record.

start sidebar
Another way to restrict data

Input masks can be useful if carefully applied, but if you decide to explore beyond the basics of Access, you will find that you can use Microsoft Visual Basic for Applications (VBA) to restrict and verify input with a lot more control than Access provides.

end sidebar
 



Online Traning Solutions - Quick Course in Microsoft Office XP
Online Traning Solutions - Quick Course in Microsoft Office XP
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 116

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net