MicrosoftВ® Office ExcelВ® 2003 Step by Step (Step By Step (Microsoft))

Overview

In this chapter you will learn to:

• Use Data Type settings to restrict data.

• Use Field Size properties to restrict data.

• Use input masks to restrict data.

• Use validation rules to restrict data.

• Use lookup lists to restrict data.

• Update information in a table.

• Delete information from a table.

Depending on how much information you have and how organized you are, you might compare a database to an old shoebox or a file cabinet, into which you toss items such as photographs, bills, receipts, and a variety of other paperwork for later retrieval. However, neither a shoebox nor a file cabinet restricts what you can place in it (other than how much can fit in it) or imposes any order on its content. It is up to you to decide what you store there and to organize it properly so that you can find it when you next need it.

When you create a database with Microsoft Office Access 2003, you can set properties that restrict what can be entered in it, thereby keeping the database organized and useful. For example, The Garden Company wouldn t want its employees to enter text into fields that should contain numbers , such as price fields. Similarly, they wouldn t want to encourage employees to enter a long text description in a field when a simple yes or no answer would work best. The field properties that control input are: Required, Allow Zero Length, Field Size, Input Mask, and Validation Rule. The Required and Allow Zero Length properties are fairly obvious. If the Required property is set to Yes , the field can t be left blank. However, if Allow Zero Length is set to Yes , you can enter an empty string (two quotation marks with nothing in between), which looks like an empty field. The other properties are more complex, so you ll focus on them in the exercises in this chapter.

To ensure the ongoing accuracy of a database, you can create and run action queries that quickly update information or delete selected records from a table. For example, The Garden Company might decide to increase the price of all products in one category, or to remove one entire product line. This type of updating is easy to do with an action query. Not only does using a query save time, but it avoids human-input errors.

The exercises in this chapter demonstrate how to use the data type setting and some of the field properties to restrict the data that can be entered in a table or form. It is difficult to experiment with field properties in a table that is already filled with information because changing a field s data type or properties can destroy or alter the data. For that reason, the first few exercises in this chapter use a new database that you will create just for the purpose of experimenting with data types and properties. Then you will resume working with sample GardenCo database files provided on the book s companion CD.