Microsoft® Access Version 2002 Step by Step

Author Online Training Solutions, Inc. Pages 352 Disk 1 Companion CD(s) Level Beg/Int Published 06/06/2001 ISBN 9780735612990 Price $29.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Index Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 6: Keeping Your Information Accurate continued

Using a Lookup List to Restrict Data

Minor inconsistencies in the way data is entered might not be really important to someone who later reads the information and makes decisions. Most people know that Arizona and AZ refer to the same state. But a computer is very literal, and if you tell it to create a list so that you can send catalogs to everyone living in AZ, the computer won’t include anyone whose state is listed in the database as Arizona.

You can limit the options for entering information in a database in several ways:

• For only two options, you can use a Boolean field represented by a check box. A check in the box indicates one choice, and no check indicates the other choice.
• For several mutually exclusive options on a form, you can use option buttons to gather the required information.
• For more than a few options, a combo box is a good way to go. When you click the down arrow at the end of a combo box, a list of choices is displayed. Depending on the properties associated with the combo box, if you don’t see the option you want, you might be able to type something else, adding your entry to the list of possible options displayed in the future.
• For a short list of choices that won’t change often, you can have the combo box look up the options in a list that you provide. Although you can create a lookup list by hand, it is a lot easier to use the Lookup Wizard to do it.

In this exercise, you will use the Lookup Wizard to create a list of months from which the user can choose. You might use something like this to gather credit card information. The working folder for this exercise is SBS\Access\Accurate\Lookup. Follow these steps:

1. Open the Field Test database located in the working folder.
2. Open the Field Property Test table in Design view.
3. Add a new field below LastName. Name it Month, and set the data type to Lookup Wizard.

The first page of the Lookup Wizard is displayed:


Click to view graphic

You can use this wizard to create a combo box that provides the entry for a text field. The combo box list can come from a table or query, or you can type the list in the wizard.

---

TIP:
If a field has a lot of potential entries, or if they will change often, you can link them to a table. (You might have to create a table expressly for this purpose.) If the field has only a few items and they won’t change, typing the list in the wizard is easier.

---

4. Click I will type in the values that I want, and then click Next.

A combo box typically has only one column, but it can have more. On this page, you can set the number of columns and then enter the text that should appear in each one. If you specify more than one column, you also have to specify which column’s text should be entered in the field when a selection is made from the list.

5. Leave the number of columns set to 1, and click in the Col1 box.
6. Enter the 12 months of the year, pressing Tab to create new rows as you need them. Then click Next.
7. Accept the Month default label, and click Finish.
8. Click the Lookup tab in the Field Properties section to view the Lookup information for the Month field, which looks as shown on the next page.


Click to view graphic

The wizard entered this information, but you could easily figure out what you would have to enter to create a lookup list by hand.

9. Click the View button to change to Datasheet view, saving your changes.
10. Adjust the column widths so that you can see all the fields, by dragging the vertical bars between columns in the header.
11. Click in the Month field of a record, and then click the down arrow to display the list, which looks like this:


Click to view graphic

12. Click February to enter it in the field.
13. Click in the next Month field, type Jan, and press Enter.

As soon as you type the J, the combo box displays January. If you had typed Ju, the combo box would have jumped to June.

14. In the next Month field, type jly, and press Enter.

The entry is accepted just as you typed it. Although there might be times when you want to allow the entry of information other than the items on the list, this isn’t one of those times.

15. Return to Design view.

The last property on the Lookup tab is Limit To List. It is currently set to No, which allows people to enter information that isn’t on the list.

16. Change Limit To List to Yes.
17. Save the table, return to Datasheet view, type jly in a new Month field, and press Enter.

Access informs you that the text you entered is not on the list, and refuses the entry.

18. Click OK, press Escape to close the list, remove your entry, and then return to Design view.

A list of the names of months is convenient for people, but if your computer has to deal with this information in some mathematical way, a list of the numbers associated with each month is easier for it to use. There is a solution that will work for both humans and machines.

19. Create a new field named Month2, and again set the data type to Lookup Wizard.
20. Click I will type in the values that I want, and click Next.
21. Type 2 to add a second column, and then click in the Col1 box.
22. Enter the following numbers and months in the two columns, pressing Tab to move from column to column:

Number	Month	Number	Month
1	January	7	July
2	February	8	August
3	March	9	September
4	April	10	October
5	May	11	November
6	June	12	December

The wizard now looks as shown on the next page.


Click to view graphic

23. Click Next to move to the next page.
24. Accept the default selection of Col1 as the column whose data you want to enter when a selection is made from the list, and click Finish.

You return to the table, with the Field Properties section displaying the Lookup information, like this:


Click to view graphic

The wizard has inserted your column information into the Row Source box and set the other properties according to your specifications.

25. Change Limit To List to Yes.
26. Save your changes, switch to Datasheet view, and then click the down arrow in a Month2 field to display this list:


Click to view graphic

27. Click January.

Access displays the number 1 in the field, which is useful for the computer. However, people might be confused by the two columns and by seeing something other than what they clicked or typed.

28. Switch back to Design view, and in the Column Widths box, change the width for the first column to 0" to prevent it from being displayed.
29. Save your changes, return to Datasheet view, and as a test, set Month2 to February in two records and to March in one record.

Only the name of the month is now displayed in the list, and when you click a month, that name is displayed in the field. However, Access actually stores the associated number from the list’s first column.

30. Right-click in the Month2 column, click Filter For on the shortcut menu, type 2 in the box, and press Enter.

Only the two records with February in the Month2 field are now displayed.

31. Click the Remove Filter button, and then repeat the previous step, this time typing 3 in the box to display the one record with March in the Month2 field.
32. Close the Field Text database, clicking Yes when prompted to save your changes.
33. Close the database.

Top of Page

Last Updated: Saturday, July 7, 2001