Access for Windows 97 for Dummies

( 1 )

Overview

Being a normal human being, you probably have work to do. In fact, you may have lots of work piled precariously around your office or even stretching onto the Internet. Relief is in sight because Access 97 can help you get more done in less time, eliminate the piles, and generally make the safety inspector happy. The good news is that you don't have to know (or even care) about table design, field types, relational databases, or any of that other database stuff to make Access 97...

See more details below
Paperback
$17.90
BN.com price
(Save 18%)$21.99 List Price
Other sellers (Paperback)
  • All (51) from $1.99   
  • New (5) from $11.67   
  • Used (46) from $1.99   
Sending request ...

Overview

Being a normal human being, you probably have work to do. In fact, you may have lots of work piled precariously around your office or even stretching onto the Internet. Relief is in sight because Access 97 can help you get more done in less time, eliminate the piles, and generally make the safety inspector happy. The good news is that you don't have to know (or even care) about table design, field types, relational databases, or any of that other database stuff to make Access 97 work for you.

Get the help you need with this friendly reference to all the features of Access 97. If you're confused instead of organized, befuddled instead of productive, or just completely lost on the whole database thing, Access 97 For Windows For Dummies is the book for you. This guide is for those who need to

  • Save time and effort by adapting existing reports and forms for new databases
  • Unearth the mysteries of solving problems with Access queries
  • Glean enough about macro programming to make people think you're a certified Access guru
  • Find hundreds of tips on making Access easier, more understandable, and generally less annoying

Start with an overview of both database concepts in general and Access 97 in particular, and start taking control of your data. Access 97 For Windows For Dummies also covers the following topics and more:

  • Dealing with data in need of repair
  • Making your table think with formats, masks, and validations
  • Finding stuff in your tables
  • Comparing AND to OR
  • Creating an AutoReport
  • Formatting a beautiful report
  • Creating forms that look cool and work great

Even after you've mastered the nuts and bolts of database building with Access 97, you'll find plenty of valuable resource material in this guide to keep you coming back for more. Timesaving keyboard shortcuts, warnings for how to avoid common crises, and an introduction to the wider world of advanced Access features are all included for you in this easy-to-use book.

Read More Show Less

Product Details

  • ISBN-13: 9780764500480
  • Publisher: Wiley
  • Publication date: 1/28/1997
  • Series: For Dummies Series
  • Edition number: 1
  • Pages: 384
  • Product dimensions: 7.30 (w) x 9.30 (h) x 0.80 (d)

Meet the Author

John Kaufeld is the author of For Dummies® guides to Paradox and Fox Pro as well as the bestselling America Online For Dummies®.

Read More Show Less

Read an Excerpt

Chapter 27
Ten Tips from the Database Nerds

In This Chapter
  • Cool ideas from that most uncool of all populations

Like ''em or loathe 'em, the technical experts are always with you. Everywhere you turn, you see someone who may know more about technology than you do. These folks sometimes look funny, frequently act strange, and can often seem amazingly disconnected from reality.

In their more lucid moments, though, the technical experts possess some nuggets of wisdom. This chapter is a distillation of good advice that I picked up over the years. Some of it is very focused, while other parts are downright philosophical. Such is life with the technical experts (but you knew that already).

Document As If Your Life Depends on It

Yes, it''s a pain. Yes, it's a bother. Yes, I do it myself (kinda scary when a guy actually listens to his own advice). If you build a database, make sure that you document every little detail about it. Here''s a list of items to start with:

  • General information about the database: Include file locations, an explanation of what the database does, and information on how it works.
  • Table layouts, including field names, sizes, contents, and sample contents: If some of the data comes from esoteric or temporary sources (like the shipping report that you shred right after data entry), note that fact in the documentation so that people know.
  • Report names, an explanation of the information on the report, and lists of who gets a copy of the report when it''s printed: If you need to run some queries before doing a report, document the process(or better yet, get a nerd to help you automate the whole thing). Documenting who receives the report is particularly important. Jot down the job title in the documentation as well as the current person in the position.
  • Queries and logic: For every query, provide a detailed explanation of how the query works, especially if it involves multiple tables.
  • Miscellaneous details: Provide information such as the backup process and schedule, where back-up tapes are located (you are doing backups, right?), and what to do if the computer isn''t working. If your database runs a particularly important business function, such as accounting, inventory, point-of-sale, or order entry, make sure that some kind of manual process is in place to keep the business going if the computer breaks down — and remember to document the process!

One final thought: Keep the documentation up to date. Every few months, review your documentation to see whether some updates are needed. Documentation is only useful if it''s up to date and if someone other than yourself can understand it. Likewise, make sure you (or your counterparts in the department) know where the documentation is located. If you have an electronic version, keep it backed up and have a printout handy.

Don''t Make Your Fields Way Too Big

When you''re building a table, take a moment to make your text fields the appropriate size for the data you''re keeping in those fields. By default, Access 97 sets up text fields to hold 50 characters — a pretty generous setting, particularly if the field happens to be holding two-letter state abbreviations. Granted, 48 characters of space aren''t anything to write home about, but multiply that space across a table with 100,000 customer addresses in it and you get 4.8MB of storage space that''s very busy holding nothing.

Adjust the field size with the Field Size setting on the General tab in Design view.

Real Numbers Use Number Fields

Use number fields for numbers, not for text pretending to be a number. Computers perceive a difference between the postal code 47201 and the number 47,201. The postal code is stored as a series of five characters that all happen to be digits, but the number is stored as an actual number. You can do math with it (just try that on a postal-code field, sometime) and all kinds of fun stuff.

When choosing the type for a new field with numbers in it, ask yourself a simple question: Are you ever going to make a calculation or do anything math-related with the field? If so, use a number type. If not, store the field as text and go on with your life.

Better Validations Make Better Data

Validations work hand in hand with masks to prevent bad data from getting close to your tables. Validations are easy to make, quick to set up, and ever vigilant (even when you''re so tired you can't see straight). If you aren''t using validations to protect the integrity of your database, you really should start. Flip to Chapter 7 and have a look at the topic.

Use Understandable Names

When building a table or creating a database, think about the names you use. Will you remember what they mean three months from now? Six months from now? Are they intuitive enough for someone else to look at the table and figure out what it does, long after your knowledge of Access 97 puts your career on the fast track?

Now that Windows 95 finally offers long filenames, please use them. You don''t need to get carried away, but now you have no excuse for files called 97Q1bdg5. Using Q1 1997 Budget Rev 5 makes much more sense to everyone involved.

Take Great Care When Deleting

Whenever you''re deleting records from a table, make sure that you're killing the right record, check again, and — only when you''re sure — delete the original. Even then, you can still do a quick Ctrl+Z and recover the little bugger.

Why all the checking and double-checking? Because after you delete a record and do anything else in the table, Access 97 completely forgets about your old record. It''s gone, just as if it never existed. If that record happened to be important and you don''t have a current back-up file, you''re out of luck. Sorry!

Keep Backups

There''s no substitute for a current backup of your data, particularly if the data is vital to your company. Don''t believe me? Let the phrase no receivables float through your mind for a while. How do you feel about backups now? I thought you''d see it my way.

Think First and Then Think Again

Apply this rule to any Access 97 step that contains the word delete or redesign. Think about what you''re doing. Then think again. Software makes handling large amounts of data easier than ever before, but it also offers the tools to screw up your data on a scale not seen since the time of P.T. Barnum.

Thomas Watson, Sr., the president of IBM for years and years, said it best: "Think."

Get Organized and Keep It Simple

Although they may seem different at first blush, these two tips work together to promote classic nerd values like a place for every gadget and my query ran faster than yours, so there. By keeping your computer orderly and organizing your entire workspace, you have everything you need at hand. Get yourself a Barcalounger and a remote control, and you never need to leave the office again.

But you can get too organized. In fact, doing so is altogether too easy. Temper your desire to organize with a passion for doing work with as few steps as possible. On your computer, limit the number of folders and subfolders you use — a maximum of five levels of folders is more than enough for just about anybody. If you go much beyond five levels, your organization starts bumping into your productivity (and nobody likes a productivity loss, least of all the people who come up with those silly little slogans for the corporate feel-good posters).

Know When to Ask for Help

If you''re having trouble with something, swallow your ego and ask for help. Saying I don''t know and then trying to find out holds no shame. This rule is especially important when you're riding herd on thousands of records in a database. Small missteps are magnified and multiplied, so ask for help before the situation becomes dire.

Access 97 For Windows® For Dummies®, Page 100

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:

  • 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.

  • 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.

  • 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.

  • 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...

Read More Show Less

Table of Contents

Introduction.

PART I: Which Came First: The Data or the Base?

Chapter 1: The 37-Minute Overview.

Chapter 2: Finding Your Way Around like a Native.

Chapter 3: Calling the Online St. Bernard and Other Forms of Help.

PART II: Truly Tempting Tables.

Chapter 4: Designing and Building a Home for Your Data.

Chapter 5: Indexes, Keys, and Relationships: Why You Care.

Chapter 6: New Data, Old Data, and Data in Need of Repair.

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

Chapter 8: Making Your Datasheets Dance.

Chapter 9: Table Remodeling Tips for the Do-It-Yourselfer.

PART III: Finding the Ultimate Answer to Everything (Well, Not Everything).

Chapter 10: Quick Searches: Find, Filter, and Sort.

Chapter 11: Make a Simple Query, Get 10,000 Answers.

Chapter 12: Searching a Slew of Tables.

Chapter 13: Lions AND Bears OR Tigers? Oh, My!

Chapter 14: Teaching Queries to Count.

Chapter 15: Automated Editing for Big Changes.

Chapter 16: Action Queries to the Rescue.

PART IV: Turning Your Table into a Book.

Chapter 17: AutoReport: Like the Model-T, It's Clunky but It Runs.

Chapter 18: Wizardly Help with Labels, Charts, and Multilevel Reports.

Chapter 19: It's Amazing What a Little Formatting Can Do.

Chapter 20: Headers and Footers for Groups, Pages, and Even (Egad) Whole Reports.

PART V: Wizards, Forms, and Other Mystical Stuff.

Chapter 21: Spinning Your Data into (and onto) the Web.

Chapter 22: Making Forms That Look Cool and Work Great.

Chapter 23: If Love Is Universal, Why Can't I Export to It?

Chapter 24: The Analyzer: Your Data's Dr. Freud, Dr. Watson, and Dr. Jekyll.

PART VI: The Part of Tens.

Chapter 25: Ten Timesaving Keyboard Shortcuts.

Chapter 26: Ten Common Crises and How to Survive Them.

Chapter 27: Ten Tips from the Database Nerds.

Chapter 28: Ten Sights to See in Your Copious Free Time.

Appendix: Installing Access 97.

Index.

Read More Show Less

First Chapter

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.

Hey Access, save my place!

This tip is a certified Nerd Trick, but it's so useful I had to take the chance and tell you about it. When entering data, sometimes you need to skip a text field because you don't have that particular information at hand. Wouldn't it be great if Access 97 automatically marked the field as blank as a reminder for you to come back and fill in the info later?

Access 97 can create such a custom text format for you, and you don't even have to be a master magician to pull off this trick. Here's how to do it: Type @;"Unknown"[Red] into the field's Format text box.

This peculiar notation displays the word Unknown in red print if the field does not contain a value. You must type the command exactly like the example (quotation marks, square brackets, and all), or it doesn't work. Feel free to substitute your own word for Unknown, though -- the command doesn't care what you put between the quotation marks.

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.

The exclamation point: To know it is to love it

Getting to know the exclamation point took me a while. After all, my input masks seemed very happy without it. Even the explanation in the Access 97 online Help file didn't change my mind. (I suppose that if the Help file's explanation had made sense, it may have had a better chance.)

While playing with the phone number example, I finally realized what the exclamation point does and why it's so useful. The exclamation point tells Access 97 to fill up the field from the right instead of the left. Although this notion may sound like the unintelligible ramblings of an over-caffeinated nerd, it really is an important point. Let me show you why.

In the phone number example, the area code is optional, but the number itself is required. If I leave the exclamation point out of the input mask, Access 97 lets me skip the area code and type a phone number into the phone number spaces. Everything looks fine until I press Enter. Then my seven-digit phone number displays as (555) 121-2. Eww -- not exactly what I had in mind. That's because Access 97 filled the mask from the left, starting with the optional numbers in the area code (the numbers I didn't enter).

By adding the exclamation point to the input mask, Access 97 takes my data and fills the mask from the right. This time, the phone number appears on-screen as ( ) 555-1212, which is what I wanted all along.

By the way, the exclamation point can go anywhere in the input mask, but try to get into the habit of putting it either at the beginning or the end. I suggest making the exclamation point the first character in the mask, simply because you won't overlook it in that position.

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.

Table 7-1 Codes for the Input Mask

Kind of Characters Required Code Optional Code
Digits (0 to 9) only 0 (zero) 9
Digits and +/- signs (not available) # (U.S. pound sign)
Letters (A to Z) only L ? (question mark)
Letters or digits only A a (must be lowercase)
Any character or space & (ampersand) C

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!

Table 7-2 Validations for Many Occasions

Field Type Validation Rule Definition
Number > 0 Must be greater than zero
Number <> 0 Cannot be zero
Number > 0 And < 100 Must be between 0 and 100 (noninclusive)
Number >= 0 And <= 100 Must be between 0 and 100 (inclusive)
Number <= 0 Or >= 100 Must be less than 0 or greater than 100 (inclusive)
Date >= Date () Must be today's date or later
Date >= Date () Or Is Null Must be today's date, later, or blank
Date < Date () Must be earlier than today's date
Date >= #1/1/90# And <= Date () Must be between January 1, 1990, and today (inclusive)
Read More Show Less

Customer Reviews

Average Rating 4
( 1 )
Rating Distribution

5 Star

(0)

4 Star

(1)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted February 17, 2000

    (10=Best)Technical - 8 / Easy to Follow - 6

    This is a very good book for the mission it is trying to accomplish. Do not make it your only book for Learning Access, however the author does attempt to bring the begginer into the world of Access and Programming in Access. Don't be fooled, it is ACCESS PROGRAMMING not just Learning Access. I would buy it again, now having completed reading the book, and it will sit upon my shelf as a Technical Dog Eared Reference. As a set of Books, this would be good to add to your collection. IS NOT a good book, to simply create Quick, Fast Databases using Access. Investigate simpler text for those task....

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)