Works: How to Sort a Database with Hyphenated Zip Codes

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 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.

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.

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.