Date, Phone Number, and Currency fields are merged incorrectly when you use an Access or Excel data source in Word

Article translations Article translations
Article ID: 304387 - View products that this article applies to.
This article was previously published under Q304387
For a Microsoft Word 97 and Word 2000 version of this article, see 142340.
Expand all | Collapse all

On This Page

Symptoms

When you perform a mail merge in Microsoft Word by using OLEDB with a Microsoft Access or Microsoft Excel data source, the merge results for the Date, Phone Number, and Currency fields are incorrect. For example:

The Date field is displayed in the following format:
1995-10-22 00:00:00
The Phone Number field from an Access data source is displayed in the following format:
-7081234567.00
The Currency field is displayed in the following format:
12.00

-or-

12000

Cause

This behavior occurs because the data is displayed in its native, stored format in Access or Excel. To format merged data, you must format the merge fields in the main document. If you format the data in the data source, the formatting is not retained when you merge the data into the main document.

Workaround

To work around this problem, use one of the following methods.

In Microsoft Word

Use a switch to format the results of the MERGEFIELD. To do this, use one of the following methods.

Note To view the field codes in Microsoft Word, press ALT+F9.

Method 1: Use the Numeric Picture Switch

Include a numeric picture switch (\#) similar to one of the following examples:

In a currency field, use a Numeric Picture switch similar to:
{MERGEFIELD number \# $####,0.0}
Note The {MERGEFIELD number \# $####,0.0} string works for Microsoft SQL Server 6 as well.

In a telephone field, use a Numeric Picture switch similar to:
{MERGEFIELD phone \# ###'-'###'-'####}
Note The hyphen inside the mergefield is in single quotation marks. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
121938 Hyphens don't display correctly with numeric picture switch

Method 2: Use the Date-Time Picture Switch

Include a date-time picture switch (\@) in the Date field, so that the Date field looks like this:
{MERGEFIELD date \@ "MMMM d, yyyy"}
Note The picture switch is case-sensitive. Additionally, you must use quotation marks around the picture string.

In Microsoft Access

Create a query in Microsoft Access, and use the query in your Word mail merge. In the Microsoft Access query, use the FORMAT() function to format the data to be merged in Microsoft Word. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
197520 How to use a query to format data for a mail merge

More information

By default, Word uses OLEDB to connect to the data source when performing a mail merge. You can force Word to use Dynamic Data Exchange (DDE) by using programming code to access the data.

For additional information about using DDE to access the data in a mail merge, click the following article number to view the article in the Microsoft Knowledge Base:
289830 Prompt to select table with Word 2002 mail merge code for Excel or Access data source
NoteFor more information about using field switches in Microsoft Word, click Microsoft Word Help on the Help menu, type general switches in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 304387 - Last Review: September 26, 2012 - Revision: 7.0
Applies to
  • Microsoft Word 2002
  • Microsoft Office Word 2007
  • Microsoft Office Word 2003
  • Microsoft Word 2010
Keywords: 
kbformat kbemail kbinterop kbmerge kbprb KB304387

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com