Works: How to Sort a Database with Hyphenated Zip Codes (110951)


The information in this article applies to:

  • Microsoft Works 3.0
  • Microsoft Works 4.5
  • Microsoft Works 4.5a
  • Microsoft Works for Windows 95, version 4.0
  • Microsoft Works for Windows 95, version 4.0 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.
Modification Type:MajorLast Reviewed:11/15/2004
Keywords:kbprb KB110951
©2004 Microsoft Corporation. All rights reserved.