Access 2013: The Missing Manual

Chapter 4. Blocking Bad Data

Even the best database designer has spent sleepless nights worrying about the errors that could be lurking in a database. Bad data is a notorious problem—it enters the database, lies dormant for months, and appears only when you discover you’ve mailed an invoice to a customer named “Blank Blank” or sold a $4.99 bag of peanuts for $499.

The best way to prevent these types of problems is to stop bad data from making it into your database in the first place. In other words, you need to set up validation rules that reject suspicious values as soon as someone types them in. Once bad data has entered your database, it’s harder to spot than a blueberry in a swimming pool.

This chapter covers the essential set of Access data validation tools:

• Duplicates, required fields, and default values are the basics of data integrity.

• Input masks format ordinary text into patterns, like postal codes and phone numbers.

• Validation rules lay down strict laws for unruly fields.

• Lookups limit values to a list of preset choices.

There’s one validation technique that this chapter doesn’t cover: using data macros. Data macros are specialized routines that spring into action when someone makes a change in your database. They’re remarkably powerful, but you can’t use them until you learn the basics of macro programming. In the meantime, the validation tools you’ll pick up in this chapter are simpler and easier to maintain.

Note

You’ll learn how to build macros in Chapter 15. You’ll learn ...