Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 6. Keeping Your Information Accurate

Chapter 6 at a Glance

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.

Tip

Each property has many options. For more information about how to use properties, search for field property in Access online Help.

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.

Important

Before you can use the practice files in this chapter, you need to install them from the book’s companion CD to their default location. See "Using the Book’s CD-ROM" for more information.

Using Data Type Settings to Restrict Data

The Data Type setting restricts entries to a specific type of data: text, numbers, dates, and so on. If, for example, the data type is set to Number and you attempt to enter text, Access refuses the entry and displays a warning.

In this exercise, you will create a new blank database, add fields of the most common data types, and experiment with how the Data Type setting and Field Size property can be used to restrict the data entered into a table.

BE SURE TO start Access before beginning this exercise.

In the New File task pane, click Blank Database in the New section to display the File New Database dialog box.
If the New File task pane does not appear, on the toolbar, click the New button.
In the File name box, type FieldTest, navigate to the My DocumentsMicrosoft PressAccess 2003 SBSAccurateDataType folder, and then click Create.
Access opens the database window for the new database.
Double-click Create table in Design view.
A blank Table window opens in Design view so that you can define the fields that categorize the information in the table. You will define five fields, one for each of the data types: Text, Number, Date/Time, Currency, and Yes/No.
Click in the first Field Name cell, type TextField, and press to move to the Data Type cell.
The data type defaults to Text, which is the type you want. Press twice to accept the default data type and move the insertion point to the next row.
Type NumberField, and press to move to the Data Type cell.
Click the down arrow to expand the list of data types, click Number, and then press twice.
Tip
Rather than displaying the list of data types and clicking one, you can type the first character of the desired type, and it will be entered in the cell.
Repeat steps 4 through 7 to add the following fields:
Field
Data type
DateField
Date/Time
CurrencyField
Currency
BooleanField
Yes/No
Tip
The data type referred to as Yes/No in Access is more commonly called Boolean (in honor of George Boole, an early mathematician and logistician). This data type can hold either of two mutually exclusive values, often expressed as yes/no, 1/0, on/off, or true/false.
Click the Save button, type Field Property Test to name the table, and then click OK.
Access displays a dialog box recommending that you create a primary key.
You don’t need a primary key for this exercise, so click No.
Click the row selector for TextField to select the first row.
The properties of the selected field are displayed in the lower portion of the dialog box.
Click in each field and review its properties, and then click the View button to display the table in Datasheet view.
The insertion point should be in the first field. Type This entry is 32 characters long, and press to move to the next field.
Type Five hundred, and press .
The data type for this field is Number. Access displays an alert box refusing your text entry.
Click OK, replace the text with the number 500, and press .
Type a number or text (anything but a date) in the date field, and press . When Access refuses it, click OK, type Jan 1, and press .
The date field accepts almost any entry that can be recognized as a date, and displays it in the default date format. Depending on the format on your computer, Jan 1 might be displayed as 1/1/2003 or 1/1/03.
Tip
If you enter a month and day but no year in a date field, Access assumes the date is in the current year. If you enter a month, day, and two-digit year from 00 through 29, Access assumes the year is 2000 through 2029. If you enter a two-digit year that is greater than 29, Access assumes you mean 1930 through 1999.
Type any text or a date in the currency field, and press . When Access refuses the entry, click OK, type –45.3456 in the field, and press .
Access stores the number you entered but displays ($45.35), the default format for displaying negative currency numbers.
Tip
Access uses the regional settings in the Microsoft Windows Control Panel to determine the display format for date, time, currency, and other numbers. If you intend to share database files with people in other countries, you might want to create custom formats to ensure that the correct currency symbol is always displayed with your values. Otherwise, the numbers won’t change, but displaying them as dollars, pounds, pesos, or euros will radically alter their value.
Enter text or a number in the Boolean field. Then click anywhere in the field to toggle the check box between Yes (checked) and No (not checked), finishing with the field in the checked state.
This field won’t accept anything you type; you can only switch between two predefined values.
Tip
In Design view, you can open the Properties dialog box, and on the Lookup tab, set the Boolean field to display as a check box, text box, or combo box. You can also set the Format property on the General tab to use True/False, Yes/No, or On/Off as the displayed values in this field (though the stored values will always be -1 and 0).
Close the table.