Using Masks to Block Bad Data - Input Masks
(Page 2 of 4 )
As you’ve already learned, databases prize consistency. If you have a field named Height, you better be sure every value in that field uses the same type of measurements; otherwise, your data’s not worth its weight in sock lint. Similarly, if you have a PhoneNumber field, you better make sure every phone number has the same format. If some phone numbers are written with dashes, spaces, and parentheses (like (844) 547-1123), while others are a bit different (say 847-547-1123), and a few leave out the area code information altogether (547-1123), then you’ve got a small problem on your hands. Because of the lack of consistency, you’ll have a hard time working with this information (say, searching for a specific phone number or sorting the phone numbers into different categories based on area code).
To help you manage values that have a fixed pattern—like phone numbers—you can use an input mask. Essentially, an input mask (or just mask for short) gives you a way to tell Access what pattern your data should use. Based on this pattern, Access changes the way values are entered and edited to make them easier to understand and less error-prone. Figure 4-8 shows how a mask lets Access format a series of characters as they’re being typed into a field.
Figure 4-8.
Top: Here’s a PhoneNumber field with a mask that’s ready to go. So far, the person entering the record hasn’t typed
anything. The PhoneNumber field automatically starts out
with this placeholder text.
Bottom: The mask formats the numbers as you type. If you type 1234567890 into this phone number mask, then you see the text (123) 456-7890. Behind the
scenes, the databases stores 1234567890, but the
information’s presented in the datasheet using a nicely
formatted package. That package is the mask.
You can add a mask to any field that uses the Text data type. Masks give you several advantages over ordinary text:
Masks guide data entry. When empty, a masked edit control shows the placeholders where values need to go. A phone number mask shows the text (_ _ _) _ _ _-_ _ _ _ when it’s empty, clearly indicating what type of information it needs.
Input Masks
- Masks make data easier to understand. You can read many values more easily when they’re presented a certain way. Most people can pick out the numbers in this formatted Social Security number (012-86-7180) faster than this unformatted one (012867180).
- Masks prevent errors. Masks reject characters that don’t fit the mold. If you’re using the telephone mask, you can’t use letters.
- Masks prevent confusion. With many types of data, you have several ways to present the same information. You can enter phone numbers both with and without area codes. By presenting the mask with the area code placeholder, you’re saying that this information’s required (and where it goes). It’s also obvious that you don’t need to type in parentheses or a dash to separate numbers, because those details are already there. You’ll see the same benefit if you use masks with dates, which can be entered in all sorts of different combinations (Year/Month/Day, Month-Day-Year, and so on).
Masks are best suited for when you’re storing numeric information in a text field. This scenario occurs with all sorts of data, including credit card numbers, postal codes, and phone numbers. These types of information shouldn’t be stored in number fields, because they aren’t meant to be interpreted as a single number. Instead, they’re meant to be understood as a series of digits. (If you do make the mistake of storing a phone number in a number field, you’ll find out that people can type in perfectly nonsensical phone numbers like 0 and –14 because these are valid numbers, even if they aren’t valid phone numbers. But an input mask on a text field catches these errors easily.)
Masks can’t help you with more sophisticated challenges, like data values that have varying lengths or subtle patterns. For instance, a mask doesn’t help you spot an incorrect email address.
Note: Text and Date/Time are the only data types that support masks.
Next: Using a Ready-Made Mask >>
More MS SQL Server Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Check it out today at your favorite bookstore. Buy this book now.
|
|
