Works: Mail Merge Fields Printed Without Numeric Formatting

When you print a Microsoft Works Word Processor document that contains merged database fields, the data in certain date, time, currency, phone number, or other formatted numeric fields may be printed as unformatted numbers.

This issue may occur if either of the following conditions is true:

• The data source stores unformatted data in a field and uses a data mask to apply formatting when you input or display data. This includes data from a Works database.
• One or more fields in the data source use field or number formats that do not exist in Microsoft Works.

To work around this issue, use your database or spreadsheet program to export the table you want to merge as a formatted text (.txt) file, and then insert fields from the exported text file into your Works Word Processor document. To work around this issue, create another field in the database which is formatted as text and filled with the contents of the original "problem" field, but use some built-in text functions to force the format to include the requisite dollar-sign ($) and appropriate 2 decimal places. To do this, follow these steps:

Note In the steps below, "PROBLEM_FIELD" refers to the field that will not merge correctly and "NEW_FIELD" refers to the field that you will create to carry the text data. Please substitute with the actual field names you wish to use.

1. Start Works 6 and open the database.
2. On the View menu, select List.
3. Click the gray block at the top of the PROBLEM_FIELD column.
4. On the Record menu, select Field, and then click After.
5. Rename the field to NEW_FIELD.
6. In the Format box, select Text.
   
   Do NOT enter a default value.
7. Click Add, and then click Done.
8. Click NEW_FIELD, type the following, and then press ENTER:

   ="$" & STRING(Round(PROBLEM_FIELD,2),2)
   					

   Note Remember to substitute the name of the actual problem field above.
   
   If decimal formatting still gets list, type the following, and then press ENTER:

   =IF(ROUND(PROBLEM_FIELD,0)=PROBLEM_FIELD,("$"&STRING(PROBLEM_FIELD,0)&".00"),("$"&STRING(PROBLEM_FIELD,2)))
   					

   The field in every record should be filled with textual versions of the values in the PROBLEM_FIELD including 2 decimal places and a dollar sign ($). If the value of the PROBLEM_FIELD is zero or it is left blank, then "$0.00" will appear.
9. Click Save, and then close the database.
10. Start the mail merge, but insert the NEW_FIELD where you used to insert the PROBLEM_FIELD. You will NOT need a dollar sign ($) in your document, it will come with the field.

There are similar issues with Dates only coming out in the short format (MM/DD/YY). Here is a similar formula to create textualized dates for merging with a longer format of DD Mon Year: Note The spaces are really important, (2 before Jan, 1 after Dec and 1 between each month).

This issue may also occur in Microsoft Word 2000, included in Microsoft Works Suite.

For more information about how to resolve this issue in Word 2000, click the following article number to view the article in the Microsoft Knowledge Base: