Selecting a month from a list of names 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.
In this exercise, you will use the Lookup wizard to create a two-column list of months from which the user can choose.
Note
USE the 06_MulticolumnLookup database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.
OPEN the 06_MulticolumnLookup database. Then display the Field Property Test table in Design view.
Add a new field below Month1. Name it Month2, and set the data type to Lookup Wizard.
Select the I will type in the values that I want option, and then click Next.
Type 2 to add a second column, and then click in the Col1 cell.
Access adds a second column, labeled Col2.
Enter the following numbers and months in the two columns:
Number
Month
1
January
2
February
3
March
4
April
5
May
6
June
7
July
8
August
9
September
10
October
11
November
12
December
It is not necessary to adjust the width of the columns in the Lookup wizard other than to make them visible within the wizard itself.
Click Next, and then click Finish.
In the Field Properties area, click the Lookup tab to view the Lookup information for the Month2 field.
The wizard has inserted your column information into the Row Source box and set the other properties according to your specifications.
Change Limit To List to Yes and Allow Value List Edits to No.
Save your changes, switch to Datasheet view, and then click the arrow in a Month2 field to display the list of options.
In the Month2 list, 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.
Switch back to Design view, and in the Column Widths box—which appears as 1";1"—change the width for the first column to 0 (you don’t have to type the symbol for inches) to prevent it from being displayed.
Save your changes, return to Datasheet view, and as a test, in the remaining records 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.
Right-click any cell in the Month2 column, point to Text Filters, and then click Equals.
In the Custom Filter box, type 2, and then press
.Access now displays only the two records with February in the Month2 field.
Click the Toggle Filter button, and then repeat Steps 12 and 13, this time typing 3 in the box to display the one record with March in the Month2 field.
