Chapter 7
Making Your Table Think with Formats, Masks, and Validations

In This Chapter

• Finding where the settings live
• Better formatting for prettier data
• Keeping bad data out with input masks
• Performing detailed testing through validations

Scientists have incredibly detailed, long-winded explanations of what it means to "think," but my definition is much simpler. If you see dragons in the clouds, marvel at a child's playtime adventures, or wonder what makes flowers grow, you're thinking.

Whether you use my definition or one from the experts, one thing is for sure: Access 97 tables don't think. If you have nightmarish visions of reading this chapter and then accidentally unleashing The Table That Ate Microsoft's Competitors, have no fear; it's not going to happen. (After all, if such a scenario could happen, don't you think Microsoft would've arranged it by now?)

This chapter explains how to enlist your table's help to spot and prevent bad data from getting into your table. The chapter focuses on three different tools: formats, masks, and validation rules. These three tools may sound kinda technical, but you can handle them (trust me).

Each tool has its own section, so if you're looking for specific information, feel free to jump ahead. (And pay no attention to those computers discussing philosophy in the corner.)

Finding the Place to Make a Change

The first bit of knowledge you need is where to make all these cool changes to your table. Luckily, all three options are in the same place: the General tab of the Table Design window.

Use the following steps to put your table into Design view, and then flip to the appropriate section of the chapter for the details on applying a format, input mask, or validation to a field in your table.

1. With the database file open, click on the table you want to adjust and then click Design (see Figure 7-1).

   The table flips into Design view, showing its nerdish underbelly to the world.

   By the way, if the table you want is already on-screen in Datasheet view, just click on the Design button on the far left side of the toolbar to get into Design view.

2. Click on the name of the field you want to work on.

   The General tab in the Field Properties section (the bottom half of the window) displays the details of the current field, as seen in Figure 7-2. You're ready to do your stuff!

3. Click in the appropriate box in the Field Properties section (along the bottom of the window) and type in your changes.

   Format, Input Mask, and Validation Rule each have a box. (There's also a box for Validation Text, but you have to look in the validations section later in this chapter to find out more about it -- it's a secret for now.)

4. If you want to work on other fields, go back to Step 2 and repeat the process.

   You can add one, two, or all three pieces of intelligence to a field at once. Access 97 automatically saves your changes when you click another field.

5. When you're through, close the table to save your changes.

Granted, using formats, masks, and validations involves many more details, but the steps to get started are the same no matter which tool you apply. The following sections tackle each tool individually, so continue on for full discussions of the tools' finer points.

To Format, Perchance to Better See

Formats only change the way you see your data on-screen, not how your data is actually stored in the table. Although formats don't directly catch errors, they do make your information look simply marvelous (and that's gotta be worth something these days).

Each field type has its own set of formats. Pay close attention to the type of field you're working with; applying the wrong format to a field is both pointless and frustrating (and goodness knows there are enough pointless and frustrating aspects of your computer without actively courting another one) because your data won't ever look right, regardless of how hard you try.

To prevent exactly that error, the following formatting information is organized by field type. Check the field type you're working with and then refer to the appropriate section for the available formatting options. By the way, if your format command doesn't work the first time -- that happens to me, too -- just double-check the field type and then review the format commands. In no time at all, you ferret out the problem.

Text and memo fields

You have four possibilities here. Unfortunately, no ready-made examples are built into the Format text box, unlike the other field types. I guess that means text and memo fields are tough and don't need the help.

Here are your four text and memo formatting options:

• The greater than symbol (>) makes all the text in that field appear in uppercase, regardless of how the text was entered. Although Access 97 stores the data just as it was typed, the data appears in uppercase only. To use this option, put a single greater than symbol in the Format text box.
• The less than symbol (<) does just the opposite of the greater than symbol. The less than symbol shows all that field's text in lowercase. If you entered the data in mixed case, it's still stored as lowercase. As with the greater than symbol, only the display is changed to protect the innocent. Apply this format by putting a single less than symbol in the Format text box.
• The at sign (@) forces Access 97 to display either a character or a space in the field. If the field data is smaller than the format, Access 97 adds extra spaces to fill up the format. For example, if a field uses @@@@@@ as its format but the field's data is only three characters long (such as Tim or now), Access 97 displays three spaces and then the data. If the field data is four characters long, the format pads the beginning of the entry with two spaces. See how the at sign works? (Kinda odd, isn't it?)
• The ampersand (&) is the default format. It means "display a character if there's one to display; otherwise don't do anything." Why create a special format for this option when it's what Access 97 does by default? I don't know . . . for now, it remains a mystery to me.
• By the way, you include one at sign or ampersand for each character in the field, unlike the greater than and less than symbols, which require only one symbol for the whole field.

Number and currency fields

The friendly folks at Microsoft did all the hard work for you on these two field types. They built the six most common formats into a pull-down menu right in the Format text box. To set a number or currency field format, click in the Format text box and then click on the down arrow that appears at the right side of the box. Figure 7-3 shows the pull-down menu, laden with your choices.

Each format's given name is on the left side of the menu. The other side shows a sample of how the format works. Here's a quick rundown of the most common choices:

• General Number: This format is the Access 97 default. It merely displays whatever you put into the field without making any editorial adjustments to it.
• Currency: This format makes a standard number field look just like a currency field. It shows the data with two decimal places, substituting zeros if decimals aren't present to begin with. Currency format also adds the appropriate currency sign and punctuation, according to the Regional Settings in the Windows 95 Control Panel.
• Fixed: This format locks the field's data into a specific number of decimal places. By default, this format rounds to two decimal places. To specify a different number of decimal places, use the Decimal Places setting right below the Format setting.
• Standard: This format does the same thing as Fixed, but adds a thousands separator as well. Adjust the number of decimals by changing the Decimal Places setting.
• Percent: This format is especially for the percentages of life. It turns a simple decimal percentage such as .97 into the much prettier 97%. Remember to enter the data as a decimal (.97 instead of 97); otherwise Access 97 displays some truly awesome percentages! If your percentages display only as 0.00% or 1.00%, see the next paragraph for a solution.

If your entries automatically round to the nearest whole number and always display zeros in the decimal places, change the Field Size setting (right above Format) from Long Integer to Single. This tells Access 97 to remember the decimal part of the number. By default, Access 97 rounds the number to an integer as you enter it. (Stupid computers.)

Date/time fields

Like the Number and Currency format options, date/time fields have a ready-to-use set of formats available in a pull-down menu. Click in the Format text box and then click on the down arrow that appears on the box's right side, and the menu in Figure 7-4 dutifully pops down to serve you.

The choices are pretty self-explanatory, but I do have a couple of tips for you:

• When using one of the larger formats such as General Date or Long Date, make sure that the datasheet column is wide enough to display the whole date. Otherwise, the cool-looking date doesn't make sense because a major portion of it is missing.
• If the database is used by more than one person, choosing a format that provides more information rather than one that provides less is much better. My favorite is the Medium Date format, because it spells out the month and day. Otherwise, dates such as 3/7/95 may cause confusion, because people in different countries interpret that format differently.

Yes/No fields

You can only say so much about a field with three options. Your preset formatting choices are somewhat limited, as Figure 7-5 shows. By default, Yes/No fields are set to the Yes/No formatting (programmers are so clever, sometimes). Feel free to experiment with the other options, particularly if they make more sense in your table than Yes and No.

To display your own choices instead of a boring Yes and No, you have to type a customized format. This procedure works very much like the custom text format earlier in this chapter. A good example format is something like this: ;"In stock"[Green];"REORDER"[Red]. If an item is in stock, the text In stock appears in green. Otherwise, REORDER screams a warning in bright red. Substitute your own words for mine if you like, because Access 97 displays whatever you put between the quotes without making any editorial decisions about the content.

What Is That Masked Data?

Although they have a funny name, input masks are filters that allow you to enter only certain data into a field. When they're paired with validations (covered later in this chapter), the fields in your table are very well protected against bad information.

An input mask is just a series of characters that tells Access 97 what kind of data to expect in this field. If you want a field to contain all numbers and no letters, an input mask can do the job. It can also do the reverse (all letters and no numbers) and almost any combination in between. Input masks are stored in the Input Mask area of the field's General tab, along with everything else discussed in this chapter.

Each field in an Access 97 table (except a memo field) can have its own input mask. Before creating the mask, you have to know exactly what the field's data looks like. Creating a mask that allows only letters into a field doesn't do any good if your goal is to store street addresses. Know your data intimately before messing around with input masks.

Input masks work best with short, highly consistent data. Numbers and number/letter combinations that all look alike are excellent candidates. Part numbers, stock-keeping units, postal codes, phone numbers, and Social Security numbers beg for input masks to ensure that the right data gets into the field.

You create an input mask in one of two ways: You can either type in the mask manually or ask the Input Mask Wizard for some help. As luck would have it, the Input Mask Wizard isn't terribly bright -- he only knows about text and date fields. And even then, he offers just a few options to make your life easier. To accomplish anything more means cracking your knuckles and doing it by hand.

Using the Input Mask Wizard

The Input Mask Wizard gleefully helps if you're making a mask for a phone number, Social Security number, United States zip code, or simple date and time field. Beyond those fields, he's clueless, so don't look for his help with anything other than text or time/date type fields.

To ask the wizard's help, go through these steps:

1. With the database file open, click on the table you want to work with and then click on Design.

   The table flips into Design view.

2. Click on the name of the field you want to adjust.

   The General tab in the Field Properties section (the bottom half of the window) displays the details of the current field.

3. Click on the Input Mask box.

   The cursor hops into the Input Mask box. To the right of the box, a small button with three dots appears. That's the Build button, which comes into play in the next step.

4. Click on the Build button at the right side of the Input Mask text box.

   The wizard appears, making a glorious entrance just like in Figure 7-6.

   You can use the wizard only with text and date fields. Don't tempt the wizard's wrath by rousing it to work with another kind of field.

   If Access 97 complains that the Wizard isn't installed, you didn't include the developer tools option while installing Access 97 (don't fret if you get this error message -- I did, too). To fix the problem, get out your original Access 97 or Office 97 CD-ROM and put it in your CD-ROM drive. Close Access 97 and then choose Start-->Settings-->Control Panel. In the Control Panel window, double-click Add/Remove Programs. Follow the prompts to install the Access 97 developer tools. If you're in a corporate environment and don't have the original master CD-ROM, contact your computer support folks for help.

5. Scroll through the list of available input masks to find what you want. Click on your choice and then click on Next.

   The dialog box displays the sordid code behind the mask, plus some other information (see Figure 7-7).

   If you chose the Password option (refer to Figure 7-6), nothing is left for you to do, so click on Finish.

6. If you want to play with the input mask and see whether it really does what you want, click in the Try It area at the bottom of the dialog box. When you're done, click on Finish to use the mask with your field.

   The chosen mask appears in the Input Mask area on the table design screen. (You can click on Cancel to call off the sordid mask affair and make the wizard go away.)

   If you click on Next instead of Finish, the wizard offers you an arcane choice about storing characters along with your data. The wizard wants to know whether you want the dashes, slashes, and parentheses that the input mask displays to be stored in your table along with the data you typed. The default is No, which I recommend sticking with. Click on Finish to complete the process.

Making a mask by hand

Few projects are more gratifying than making something yourself. Building an input mask with your bare hands, raw nerve, and these instructions may give you that same feeling of accomplishment. (If so, please seek professional help soon -- you're in danger of becoming a technoid.)

• The stuff that input masks do isn't terribly complicated, but a finished mask often looks complicated. Don't worry, though. After you get the hang of it, building powerful input masks is easy.
• My friends told me the same thing about water skiing, but during my first lesson, I suspected that they were really trying to drown me and make it look like an innocent water sports accident. You have my word that building input masks in Access 97 isn't anything like that. (Besides, how can you drown in front of a computer?)

With that confidence-building introduction behind you, get ready to roll up your sleeves and plunge your hands in the alphabetic goop of input masks. Designing and using an input mask takes just a few steps:

1. On a piece of paper, write an example of the data that the mask is supposed to let into the table.

   As I mention earlier in the chapter, knowing your data really is the first step in the input mask process.

   If the information you're storing has subtle variations (such as part numbers that end in either a letter/number or letter/letter combination), include examples of the various possibilities so that your input mask accepts them all.

2. Write a simple description of the data, including which elements are required and which are optional.

   If your sample is a part number that looks like 728816ABC7, write six numbers, three letters, one number; all parts are required. Remember to allow for the variations, if you have any. The difference between one number and one letter or number can be crucial.

   Required information must be entered into the field (such as a phone number). Optional elements are just that -- optional (such as an area code or extension number). Access 97 uses different codes for required and optional data, so you need to note the difference.

3. Using the codes in Table 7-1, create an input mask for your data.

   Because you know what kind of data you're storing (numbers, letters, or either one), how many characters you need, and whether each one is required or optional, working through the table and creating the mask is easy.

   To include a dash, slash, or parenthesis in your mask, put a backslash (\) in front of it. To include more than one character, put quote marks around them. For example, the mask for a phone number with an area code is !\(999") "000\-0000. This mask uses both the backslash and quote mark to put parentheses around the area code plus a space between the area code and phone number. (See the sidebar "The exclamation point: To know it is to love it" to find out why I included an exclamation point in this example.)

   Here's a pop quiz, just to see whether you're paying attention. In the example, is the area code optional or required? What about the phone number itself? Why? Write a long, detailed answer on a very small piece of paper; then rip it to shreds and throw it like confetti into the air. Wasn't that fun?

4. If your field includes letters and you want them to always be uppercase, add a greater than symbol (>) to the beginning of your mask.

   To make the letters lowercase, use a less than symbol (<) instead.

   You're ready to tell Access 97 about your input mask.

5. Follow the steps at the beginning of the chapter ("Finding the Place to Make a Change"); when you get to Step 3, click in the Input Mask box.

   The blinking toothpick cursor hops into the box, ready for action.

6. Carefully type your finished mask into the Input Mask area of Field Properties (see Figure 7-8).

   Don't worry if the mask looks like a text version of the Frankenstein monster. Beauty is optional in the world of technology.

7. At the end of the mask, add ;;_ (two semi-colons and an underscore character).

   These three characters tell Access 97 to display an underscore where you want each letter to appear. This step isn't required, but I think that input masks make more sense with this option. Your mileage may vary.

8. Click on the Table View button on the toolbar to check out your handiwork.

Try typing something into the now-masked field. The input mask should prevent you from entering something incorrectly. If it doesn't work, take the table back into Design view (click on the Design View button on the left side of the toolbar) and make some repairs.

If you're adding a mask to an existing table, the mask doesn't ferret out incorrect data that's already in the table. You have to click on each entry in the masked field (yes, that means clicking on this field in every record of the table) in order to check it. If something is wrong, Access 97 tells you, but not until you click.

Validations: The Digital Breathalyzer Test

Your third (and, arguably, most powerful) tool in the War Against Bad Data is the validation. With a validation, Access 97 actually tests the incoming data to make sure that it's what you want in the table. If the data isn't right, the validation displays an error message (you get to choose what it says!) and makes you try the entry again.

Like the other options in this chapter, validations are stored in the General tab of the Field Properties area. Two spaces relate to validations: Validation Rule and Validation Text. The rule is the actual validation itself. The text is the error message you want Access 97 to display when some data that violates the validation rule wanders in.

Validations work best with number, currency, and date fields. Creating a validation for a text field is possible, but the validations usually get very complicated very fast. In the name of protecting your sanity and hairline, Table 7-2 contains some ready-to-use validations that cover the most common needs. They're organized by field type, so finding the validation rule that suits your purpose is easy.

I include different kinds of examples to show off the power of the logical operators that validations use. Feel free to mix and match with the operators. Play around and see what you can come up with!

• When using And, remember that both sides of the validation rule must be true before the rule is met.
• With Or, only one side of the rule needs to be true for the whole rule to be true.
• Be careful when combining >= and <= examples. Accidentally coming up with one that won't ever be true (such as <= 0 And >= 100) is too easy!