Microsoft KB Archive/902331

= You cannot see the starting zero in the ZIP Code/Postal Code after you merge a Works Database field together with a Works Word Processor document =

Article ID: 902331

Article Last Modified on 10/25/2005

-

APPLIES TO


 * Microsoft Works Suite 2005
 * Microsoft Works Suite 2004
 * Microsoft Works Suite 2003
 * Microsoft Works Suite 2002
 * Microsoft Works Suite 2001
 * Microsoft Works Suite 2000
 * Microsoft Works 8.0
 * Microsoft Works 7.0
 * Microsoft Works 6.0
 * Microsoft Works Suite 2006

-





SYMPTOMS
When you merge a Microsoft Works Database field that contains a set of numbers that starts with zero (0) with a Microsoft Works Word Processor document by performing a mail merge, the mail-merge process truncates the zero at the start of the set of numbers. For example, if you have a Works Database field that stores ZIP Codes/Postal Codes that start with a zero, you cannot see the zero in the ZIP Code/Postal Code after you merge the Works Database field together with a Works Word Processor document.



CAUSE
This issue may occur if one or more of the following conditions are true:  The Works Database field that you are trying to merge contains characters that are not valid, contains formulas that are not valid, or contains a formatting option that the merge operation does not recognize, such as the following:

., [, ], {, }, !, /, \, |, #,<, >, ~, @, $

 The Works Database file format is not compatible with Works Word Processor. The Works Database file is corrupted. The Works Database field is formatted as numeric. The mail merge filter automatically removes digits from the number that are not significant. Therefore, the first zero in the number is omitted.



RESOLUTION
To resolve this issue, use one of the following methods.

Method 1: Save the Works Database file as a text and tab (*.txt) file or as a text and comma (*.csv) file
You can save the Works Database file as a text and tab (*.txt) file or as a text and comma (*.csv) file. Then, you can use this Works Database file to perform mail merge. To do this, follow these steps:
 * 1) Open the existing Works Database file that you want to modify.
 * 2) On the File menu, click Save as.
 * 3) In the Save in box, click the location where you want to save the Works Database file.
 * 4) In the File name box, type a new name for the Works Database file.
 * 5) In the Save as type box, click Text & Tabs (*.txt) or click Text & Commas (*.csv), and then click Save.

Note When you save a Works Database file as a text file, the field names, the formatting, and the formulas are not saved.
 * 1) Close the Works Database file.
 * 2) Start Works Word Processor.
 * 3) On the Tools menu, click Mail Merge, and then click Open Data Source.
 * 4) Click Merge information from another type of file.
 * 5) Locate the text Works Database file that you want to merge, and then click Open.

Note To view the text file, click All files in the Files of type box.
 * 1) When you are prompted to select the fields that you want to merge into the Works Word Processor document, click each preferred field for merging, and then click Insert.

Note You can decide not to use the entries in the first row of the Works Database.
 * 1) Follow the instructions that appear on the screen to complete the wizard.

Method 2: Change the format type of the Works Database field
If the format type that is applied to the Works Database field is numeric, you can change the format type to text so that the Works Database field value that starts with the digit zero is preserved when the Works Database field is mail merged to the Works Word Processor document. To do this, follow these steps:
 * 1) Open the existing Works Database file that you want to modify.
 * 2) Click the Works Database field that you want to change the format for.
 * 3) Sort the field so that the values that start with zeros appear on the top.
 * 4) On the Format menu, click Field.
 * 5) Under Format, click Text, and then click OK.
 * 6) Re-enter all the field values that starts with the digit zero.

Note You do not have to change Works Database field values that contain a hyphen (-) between the digits because Works automatically formats those values as text values.
 * 1) Save the Works Database file.
 * 2) Close the Works Database file.
 * 3) Start Works Word Processor.
 * 4) On the Tools menu, click Mail Merge, and then click Open Data Source.
 * 5) Click Merge information from another type of file.
 * 6) Locate the Works Database file that you want to merge, and then click Open.
 * 7) When you are prompted to select the fields that you want to merge into the Works Word Processor document, click each preferred field for merging, and then click Insert.
 * 8) Click View Results to view the records that you inserted.

Method 3: Use a formula to change the Works Database field format type
You can use this method if you have more field values to edit, and if the field values contain only numbers.  Open the existing Works Database file that you want to modify.</li> Create a new Works Database field after the original field that contains the numeric values. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> Click the Works Database field that you want to add a new Works Database field after.</li> On the Record menu, point to Insert Field, and then click After.</li> In the Field name box, type the field name for the new field.</li> Under Format, click Text, and then click Add.</li></ol> </li> Type a formula in the new field to convert the contents of the original field to text. For example, you can use the following sample formula to convert the numeric values in the original field to text:

=LEFT(&quot;00000&quot;,(5-LENGTH(STRING(Original_field,0))))&(STRING(Original_field,0))

The following are the definitions for the text in the sample formula:  =LEFT(&quot;00000&quot;, (5-LENGTH(STRING(Original_field,0)))): returns the remaining number of zeros in the &quot;00000&quot; text expression after subtracting the length of the field value that is returned in the text with the specified number of decimal spaces by the STRING(Original_field,0) formula. For example, if the numeric value is 00028, the formula returns the value 000.</li> STRING(Original_field,0): returns the numeric value in the text with the specified number of decimal spaces. For example, if the numeric value is 00028, the formula returns the value 28.</li> =LEFT(&quot;00000&quot;,(5-LENGTH(STRING(Original_field,0))))&(STRING(Original_field,0)): returns the combined result of the first two steps. For example, if the numeric value is 00028, the formula returns the text value 00028.</li></ul> </li> Save the Works Database file.</li> Close the Works Database file.</li> Start Works Word Processor.</li> On the Tools menu, click Mail Merge, and then click Open Data Source.</li> Click Merge information from another type of file.</li> Locate the Works Database file that you want to merge, and then click Open.</li> <li>When you are prompted to select the fields that you want to merge into the Works Word Processor document, click the new field that you created for merging, and then click Insert.</li> <li>Click View Results to view the inserted records.</li></ol>

<div class="moreinformation_section">

MORE INFORMATION
For more information about how to preserve leading zeros in a number in a Works Database field, click the following article number to view the article in the Microsoft Knowledge Base:

242330 Works: How to preserve leading zeros in a number in a spreadsheet cell or database field

Keywords: kbtshoot kbdatabase kbmerge kbformula KB902331

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.