Using Masks to Block Bad Data

Multifield indexes

You can also use indexes to prevent a combination of values from being repeated. Imagine you create a People table to track your friends and their contact information. You’re likely to have entries with the same first or last name. However, you may want to prevent two records from having the same first and last name. This limitation prevents you from inadvertently adding the same person twice.

Note: This example could cause endless headaches if you honestly do have two friends who share the same first and last names. In that case, you’ll need to remove the index before you’re allowed to add the name. You should think carefully about legitimate reasons for duplication before you create any indexes.

To ensure that a combination of fields is unique, you need to create a compound index, which combines the information from more than one field. Here’s how to do it:

1. In Design view, choose Table Tools | Design -> Show/Hide -> Indexes.
   
   The Indexes window appears (Figure 4-6). Using the Indexes window, you can see your current indexes and add new ones.
   
   
   Figure 4-6.
   The Indexes window shows all the indexes that
   are defined for a table. Here, there’s a single
   index for the ID field (which Access created
   automatically) and a compound index that’s in
   the process of being created.
   
2. Choose a name for your index. Type this name into the first blank row in the Index Name column.
   
   The index name has no real importance—Access uses it to store the index in the database, but you don’t see the index name when you work with the table. Usually, you’ll use the name of one or both of the fields you’re indexing (like LastName+FirstName). 
   
3. Choose the first field in the Field Name column in the same row (like LastName).
   
   It doesn’t matter which field name you use first. Either way, the index can prevent duplicate values. However, the order does affect how searches use the index to boost performance. You’ll learn more on page 195. 
   
   Input Masks
   
4. In the area at the bottom of the window, set the Unique box to Yes.
   
   This creates an index that prevents duplicates (as opposed to one that’s used only for boosting search speeds).
   
   You can also set the Ignore Nulls box to Yes, if you want Access to allow duplicate blank values. Imagine you want to make the SSN field optional. However, if an SSN number is entered, then you want to make sure it doesn’t duplicate any other value. In this case, you should Ignore Nulls to Yes. If you set Ignore Nulls to No, then Access lets only one record have a blank SSN field, which probably isn’t the behavior you want.
   
   

   ---

   Note: You can also disallow blank values altogether using the Required property, as described on page 116.

   ---

   
   Ignore the Primary box (which identifies the index used for the primary key). 
   
5. Move down one row. Leave the Index Name column blank (which tells Access it’s still part of the previous index), but choose another field in the Field Name column (like FirstName).
   
   If you want to create a compound index with more than two fields, then just repeat this step until you’ve added all the fields you need. Figure 4-7 shows what a finished index looks like.
   
   You can now close the Indexes window.
   
   
   Figure 4-7. 
   Here’s a compound index that prevents two
   people from sharing the same first and last
   names.

Input Masks

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. 

Using a Ready-Made Mask

The easiest way to get started with masks is to use one of the many attractive options that Access has ready for you. This method’s great, because it means you don’t need to learn the arcane art of mask creation.

Here’s what you need to do to pick out a prebuilt mask:

1. In Design view, select the text field where you want to apply the mask.
   
   For this test, try a PhoneNumber field. 
   
2. Look for the Input Mask field property. Click inside the field.
   
   When you do, a small ellipsis (…) button appears at the left edge, as shown in Figure 4-9. 
   
   
   Figure 4-9.
   The ellipsis (...) button (circled) is just the way Access tells you that you don’t need to fill in this value by hand. Instead, you can click the ellipsis and pop up a wizard (like the Input Mask wizard) or some sort of helpful dialog box.
   
3. Click the ellipsis button.
   
   The Input Mask wizard starts (see Figure 4-10).
   
   
   Figure 4-10.
   The Input Mask wizard starts with a short list of commonly used masks. Next to every mask, Access shows you what a sample formatted value looks like. Once you select a mask, you can try using it in the Try It text box. The Try It text box
   gives you the same behavior that your field will have once you apply the mask.
    
   
4. Choose the mask you want from the list of options.
   
   In this case, choose the first item in the list (Phone Number).
   
   

   ---

   Note: Don’t see what you want? You’ll need to create your own, using the tips on page 128. If you see one that’s close but not perfect, select it. You can tweak the mask in the wizard’s second step. 

   ---

   
   
5. Click Next.
   
   The wizard’s second step appears (see Figure 4-11). 
   
6. If you want, you can change the mask or the placeholder character.
   
   To change the mask, you’ll need to learn what every mask character means. Page 129 explains it all.
   
   Input Masks
   
   
   Figure 4-11.
   The phone number mask is !(999) 000-000. Each 9 represents an optional number from 0 to 9. Each 0 represents a required number from 0 to 9. So according to this mask, (123) 456-7890 is a valid phone number, as is 123-4567, but (123) 456
   isn’t.
   
   You use the placeholder to show the empty slots where you enter information. The standard choice is the underscore. Optionally, you can use a space, dash, asterisk, or any other character by typing it in the “Placeholder character” box. 
   
7. Click Next.
   
   If you’re adding a mask to a text field, then the wizard’s final step appears (see Figure 4-12).
   
   If you’re adding a mask to a date field, then Access doesn’t need to ask you how to store the information—it already knows. In this case, you can jump to step 9 and click Finish. 
   
   
   Figure 4-12.
   The final step lets you choose how the data in your field is chosen—with or without the mask symbols.
   
8. Choose how you want to store the value in this field.
   
   The standard choice is to store just the characters you’ve typed in (in other words, everything you type into the field). If you use this option, the placeholders aren’t included. For example, the phone number (416) 123-4567 is stored as 4161234567. This option saves a little space, and it also lets you change the mask later on to present the information in a slightly different way.
   
   You could also store the mask complete with all the extra characters. Then a phone number’s stored complete with hyphens, dashes, and spaces, like (416) 123-4567. This approach isn’t nearly as flexible because you can’t change the mask later. 
   
9. Click Finish.
   
   The final mask appears in the Input Mask field property.
   
   Before going any further, you may want to make sure that the length you’ve reserved for your field matches the mask. In the phone number example, you need a Field Size of 10 if you’ve chosen to store unformatted values (because there are 10 digits), or a Field Size of 14 for the whole shebang, complete with placeholders (one dash, one space, and two parentheses). 
   
10. Switch back to the Datasheet view, and click Yes when Access asks you to save changes.
    
    Your input mask is now in place.

Note: Access uses the input mask information to control how you enter information in the datasheet. However, it’s possible to circumvent the mask by entering the information in other ways. You could, for instance, create a form (as described in Part Four), and switch off the mask. A mask’s not an absolute guarantee against invalid data—if you want such a guarantee, then you need a validation rule instead.

Creating Your Own Mask

The Input Mask wizard provides a fairly limited set of choices. If you want to use a mask with your own type of information (like a special customer code that your business uses), then you’ll have to create your own mask.

Creating a mask’s fairly easy, but it can take a bit of fiddling before you nail down exactly the result you want. You have two basic options:

1. Type or edit the mask directly in the Input Mask field property.
   
2. Launch the Input Mask wizard, choose a mask to use as a starting point (as described on page 125), and then tweak it in step 2. This approach has the advantage that you can test your mask in the Try It box before you save it as part of your table.

Every mask’s built out of three types of characters:

1. Placeholders designate where you type in a character.
   
2. Special characters give additional instructions that tell Access how to treat a part of the mask. 
   
3. Literals are all other characters, which are really just decoration to help make the value easier to interpret.

In the previous example, the phone number mask was !(999) 000-000. The characters 9 and 0 are placeholders—they represent where you type in the digits of the phone number. The parentheses, space, and dash are just formatting niceties—they’re the literals. And the exclamation mark’s the only special character. It tells Access that characters should be entered into the mask from left to right, which is the standard option and the only one that really makes sense for a phone number.

To help you sort all this out, refer to the following tables. Table 4-1 shows all the placeholders you can use in an input mask. Table 4-2 shows other special characters. Everything else is automatically a literal character.

Table 4-1.  Special Characters for an Input Mask

Table 4-2.  Special Characters for an Input Mask

Here are a few sample masks to get you started:

1. (000) 000-000. A phone number that requires the area code digits. This mask’s different from the phone number mask that the Input Mask wizard uses. That mask replaces the first three 0 characters with 9, making the area code optional.
   
2. 00000-9999. A U.S. zip code, which consists of five required digits followed by a hyphen and (optionally) four more digits.
   
3. L0L 0L0. A British or Canadian postal code, which is a pattern of six characters that alternate between characters and digits, like M6S 3H2.
   
4. 99:00:00 >LL. A mask for entering time information into a Date/Time field. It’s made up of two digits for the hour and two digits for the minute. The last two characters are always displayed in uppercase (thanks to the > character), and are meant to be AM or PM. (Technically, this mask doesn’t prevent the user from flouting the system and typing in two different characters. However, if you enter a time like 12:30 GM, Access complains that it can’t convert your entry into the Date/Time data type, as required for the field.)
   
5. 099.099.099.099. An IP (Internet Protocol) address, which identifies a computer on a network. An IP address is written as four values separated by periods. Each value must have at least one digit, and can have up to three. This pattern’s represented in the mask by 099 (one required digit, followed by two optional digits).
   
6. Password. A mask that allows ordinary, unlimited text, with one difference. All characters are displayed as asterisks (*), to hide them from prying eyes.

Masks can also have two optional bits of information at the end, separated by semicolons (;).

The second section’s a number that tells Access whether or not it should store the literal characters for the mask in the record. (This is the last question that the Input Mask wizard asks.) If you leave this piece out or use the number 1, then Access stores only the characters that someone types in. If you use the number 0, then Access stores the full text with the literals.

The third section supplies the placeholder character. If you leave this section out, then Access uses the familiar underscore.

Here’s a mask that uses these two extra bits of information:

  (000) 000-000;1;#

Here, the second section’s 1, and the third section’s #. This mask’s for phone numbers, and it stores literals (in this case, two parentheses, a space, and a dash) and uses the number sign for a placeholder instead of the underscore.

Please check back tomorrow for the continuation of this article.