Microsoft KB Archive/110951

Knowledge Base

Works: How to Sort a Database with Hyphenated Zip Codes

Article ID: 110951

Article Last Modified on 11/15/2004

---

APPLIES TO

• Microsoft Works 3.0 Standard Edition
• Microsoft Works 4.5 Standard Edition
• Microsoft Works 4.5a
• Microsoft Works 4.0 Standard Edition
• Microsoft Works 4.0a

---

This article was previously published under Q110951

SYMPTOMS

It is often useful to sort the database according to zip code before actually printing envelopes or mailing labels.

If some of the records in the database contain zip codes formatted in the hyphenated format

ZIP + four numbers (for example, 99999-5555)

while others are in the five-digit (no hyphen) format, the records are sorted incorrectly. The five-digit zip codes will appear at the top. This happens because numbers that contain hyphens are treated as text by Works. When a database is sorted by a field that contains both text and numbers, the numbers are pushed to the top.

RESOLUTION

Try one of the following methods:

Method 1

If you format the field as text before entering the zip codes, you can then correctly sort the fields.

For more information about how to perform this task in Works, see your Works printed documentation or online Help.

Method 2

To correctly sort the database by zip code, do the following:

1. Create an additional database field.
2. In the new field, enter the formula

   =IF(N(ZipCode)=0,S(ZipCode),RIGHT("00000" & STRING(ZipCode,0),5))

   where:

   • IF(condition,valueiftrue,valueiffalse) returns whatever is specified by valueiftrue if condition is true, and so on.
     
     
   • N(ZipCode) returns 0 if the field contains text, and returns the number otherwise.
     
     
   • S(ZipCode) returns the text in the field, or returns blank if the field contains a number.
     
     
   • STRING(ZipCode,0) converts a number to a string and specifies no decimal places be displayed.
     
     
   • RIGHT(text,length) takes the rightmost portion of a string.
     
     
   • "00000" & STRING(ZipCode,0) appends five zeros to the left side of the zip code value after converting the zip code to text.
     
     
   • RIGHT("00000"&STRING(ZipCode,0),5) takes the five rightmost characters from the combined text, leaving the correct number of leading zeros.

   
   
   

3. Sort the database using the new field. The grouping should be correct.

Method 3

1. Open the database.
2. Rename the Zipcodes column to Zip.
3. Create a new colum called Zipcodes.
4. Format both columns as Text.
5. Insert the following formula for the new Zipcodes field:

   =IF((Zip*1=0),Zip,STRING(Zip,0))

6. Sort the database using the new field.

MORE INFORMATION

The above steps work because of Works ability to handle text in formulas.

For more information about how to perform these tasks in Works, see your Works printed documentation or online Help.

Additional query words: w_works 4.00 3.00 4.00a 4.50 4.50a db

Keywords: kbprb KB110951

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.