Figure 1. This graphic represents a query in

Let's assume that you run a mail-order business, and you've hired a few temporary workers to do a customer survey. You want to give each temp a list of customers broken down by region, which would be easy to do if you could divide your database according to the first two digits of the customers' ZIP codes. Although you have your customers' five-digit ZIP codes stored in your database, you don't have a field that displays only the first two digits. How can you quickly extract the first two digits from each five-digit ZIP code and display them separately? The solution is to use a particular function to extract the data you need. In this tutorial, you'll learn how to use the Left, Right, and Mid functions to find, respectively, the first two, last two, and middle three digits of a five-digit ZIP code. You can use the same functions to extract sections of telephone numbers, product parts numbers, etc. We'll also review how to create a query using simple criteria.

Create A Query

Because this tutorial requires you to create a new object in the Northwind database, we recommend that you make a copy of the file before proceeding. Navigate to the Northwind.mdb database in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the Clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can now open and use without worrying about changing the original file.

In the Database window with Tables selected, double-click Customers to open that table. Use the horizontal scrollbar to find the Country field. Click the selector at the top of the Country column to select the entire column and then click the Z-A button to apply a Descending sort. Note that several of Northwind's customers are in the United States (listed as USA in the table). To the left of the Country column, you'll see the Postal Code column, which contains five-digit ZIP codes for U.S. customers. Close the Customers table without saving your layout changes.

In the Database window, click Queries and double-click Create Query In Design View. In the Show Table dialog box, click Customers, click Add, and click Close. In the Customers field list, double-click CompanyName, PostalCode, and Country to add them to the design grid. Open the File menu, choose Save As, edit the top field by typing ZIP Code Values, and click OK.

Next, let's set the criteria so that only U.S. customers are selected. On the Criteria row of the Country column, type usa. In the same column, deselect the Show checkbox. Click the View button to see your records.

Use The Left Function

Now we can create expressions to extract various parts of the ZIP codes. Click the View button to return to Design view. In the first blank column, type TwoDigitZIP: Left ([PostalCode],2)on the first row. Here is what each part of this expression means:

•"TwoDigitZIP" is the name of the column.

•"Left" is the function that directs Access to extract values, beginning from the left side.

•"PostalCode" is the field.

•"2" is the number of characters to extract.

Click the View button to see your records. As you'll notice, Access has extracted the first two numbers of each ZIP code.

Click the View button to return to Design view. Right-click anywhere in the TwoDigitZIP column and choose Properties. In the Caption field, type Two-Digit ZIP. Close the Field Properties dialog box.

Use The Right Function

As you'll soon find out, the Right function is very similar to the Left function. Click inside the first row of the next empty column and type LocalZIP: Right ([PostalCode],2).

When you click the View button to see the column and compare it to the five-digit ZIP code, you'll see that Access has extracted two characters from the right end of each ZIP code. Also note that the TwoDigitZIP field now has the easier-to-read heading "Two-Digit ZIP." Click the View button to return to Design view (see Figure 1).

Use The Mid Function

With the Mid function, you can extract values from the middle of a text string. The Mid function is similar to the Left and Right functions, with one important difference: You have to tell Access where in the text string you want to start grabbing characters.

Click inside the first row of the next empty column and type TestZIP: Mid ([PostalCode], 2, 3). In this case, "TestZIP" is the name of the column; "Mid" is the function that directs Access to extract values; "PostalCode" is the field; "2" is the position of the first character, as counted from the left; and "3" is the number of characters to extract.

Click the View button to compare the TestZIP column with the Postal Code column (see Figure 2).

A Final Caveat

When you apply the Left, Right, or Mid function, Access extracts the text string from the value that's actually stored in the table rather than from the value as it's formatted. A phone number provides a good example of this. When you create a phone field, you'll likely set the Input Mask and/or Format properties so that the phone number displays as, for example, (415) 555-3617. However, the numbers are actually stored as, for example, 4155553617. If the parentheses and dashes were actually included in the storedphone numbers, you'd have to count them just like any of the other characters. So, using the Left function for the phone number example we provided in the previous paragraph, the first three characters would be (41 rather than 415.

Figure 2. The three columns on the right side used the Left, Right, and Mid functions to extract different characters from the customers' ZIP codes.

Home Access 2002 Forms Use Comb Boxes to Select Values for Fields
Search MS Office A-Z   |   Search Web Pages/ Design A-Z