Works: Mail Merge Fields Printed Without Numeric Formatting (246066)


The information in this article applies to:

  • Microsoft Works 8.0
  • Microsoft Works 7.0
  • Microsoft Works 6
  • Microsoft Works 2000
  • 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 Suite 2006
This article was previously published under Q246066

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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.

-or-

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: 
=STRING(DAY(Date_field),0)&MID("  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ",MONTH(Date_field),5)&STRING(YEAR(Date_field)+1900,0)
Note The spaces are really important, (2 before Jan, 1 after Dec and 1 between each month).

MORE INFORMATION

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:

211421 WD2000: Incorrect format of phone number in mail merge result

Modification Type:MajorLast Reviewed:10/25/2005
Keywords:kbprb kbprint kbui KB246066
©2004 Microsoft Corporation. All rights reserved.