Blocking Bad Data - Preventing Blank Fields
(Page 2 of 4 )
Every record needs a bare minimum of information to make sense. However, without your help, Access can’t distinguish between critical information and optional details. For that reason, every field in a new table is optional, except for the primary-key field (which is usually the ID value). Try this out with the Dolls table from Chapter 1; you’ll quickly discover that you can add records that have virtually no information in them.
You can easily remedy this problem. Just select the field that you want to make mandatory in Design view, and then set the Required field property to Yes (Figure 4-1).
Figure 4-1.
The Required field property tells Access not to allow empty values (called nulls in tech-speak).
Access checks the Required field property whenever you add a new record or modify a field in an existing record. However, if your table already contains data, there’s no guarantee that it follows the rules.
Imagine you fill the Dolls table with a few bobbleheads before you decide that every record requires a value for the Character field. You switch to Design view, choose the Character field, and then flip the Required field property to Yes. When you save the table (by switching back to Datasheet view or closing the table), Access gives you the option of verifying the bobblehead records that are already in the table (Figure 4-2). If you choose to perform the test and Access finds the problem, it gives you the option of reversing your changes (Figure 4-3).
Figure 4-2.
It’s a good idea to test the data in your table to make sure it meets the new requirements you put into place. Otherwise, invalid data could still remain. Don’t let the message scare you—unless you have tens of thousands of records, this check doesn’t take long.
Figure 4-3.
If Access finds an empty value, then it stops the search and asks you what to do about it. You can keep your changes (even though they conflict with at least one record)—after all, at least new records won’t suffer from the same problem. Your other option is to reset your field to its more lenient previous self. Either way, you can track down the missing data by performing a sort on the field in question (page 94), which brings empty values to the top.
WORD TO THE WISE
Don't Require Too Much
You’ll need to think very carefully about what set of values you need, at a minimum, to create a record.
For example, a company selling Elvis costumes might not want to accept a new outfit into their Products table unless they have every detail in place. The Required field property’s a great help here, because it prevents half-baked products from showing up in the catalogue.
On the other hand, the same strictness is out of place in the same company’s Customers table. The sales staff needs the flexibility to add a new prospect with only partial information. A potential customer may phone and leave only a mailing address (with no billing address, phone number, email information, and so on). Even though you don’t have all the information about this customer, you’ll still need to place that customer in the Customers table so that he or she can receive the monthly newsletter.
As a general rule, make a field optional if the information for it isn’t necessary or might not be available at the time the record is entered.
Next: Blank values and empty text >>
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.
|
|
