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

This article was previously published under Q142340
This article has been archived. It is offered "as is" and will no longer be updated.
When you perform a mail merge using ODBC with a Microsoft Access orMicrosoft Excel data source, the merge results for Date, Phone Number, andCurrency fields are incorrect. For example, the Date field appears in thefollowing format:
1995-10-22 00:00:00
The Currency field appears in the following format:
The Phone Number field from Microsoft Access appears in the followingformat:
The data appears in its native, stored format in Microsoft Access orMicrosoft Excel. When you use DDE to convert the Microsoft Access orMicrosoft Excel data instead of ODBC, the data is "masked" to displaythe correct format.

Method 1: Use DDE to connect to the Microsoft Access or Excel database

To do this, select the Select Method (Confirm Conversion) check box in theOpen Data Source dialog box, locate and select your Access/Excel database,and then click Open. In the Confirm Data Source dialog box, selectMicrosoft Access or Microsoft Excel Database via DDE (*.mdb) and click OK.

NOTE: If DDE is not available, please see the following article in theMicrosoft Knowledge Base:
137076 WD: Can't Use Microsoft Access Files (.mdb or DDE) with Word

Method 2: Use MyDateFieldToLong

In the Access database, type the following statement for the date column
MyDateFieldToLong: Format([MyDateField],"Long Date")
where MyDateFieldToLong is the actual name of the field being used to label the date column.

Method 3: Use ODBC and add a picture switch to the Date/Currency fields

Include a numeric picture switch (\#) in the Currency field, so that theCurrency field looks like this:
{mergefield number \# $####,0.0}
NOTE: The {mergefield number \# $####,0.0} string works for SQL Server 6as well.

Include a numeric picture switch (\#) in the Phone Number field, so thatthe Phone Number field looks like this:
(mergefield phone \# ###'-'###'-'####}
NOTE: The hyphen inside the mergefield is in quotation marks. Also, thequotation marks are single quotation marks.For more information, please see the following article in the MicrosoftKnowledge Base:
121938 WD: Hyphens Don't Display Correctly with Numeric Picture Switch
Include a date-time picture switch (\@) in the Date field, so that the Datefield looks like this:
{mergefield date \@ "MMMM d, yyyy"}
NOTE: The picture switch is case-sensitive; also, you must use quotationmarks around the picture string.
winword access database data source odbc dde mail merge print merging merged records results fields mergefield currency money dollars $ date time wrong incorrect change how to switch picture numeric number decimal thousandths telephone word97 access97 excel97 excel8

Article ID: 142340 - Last Review: 02/28/2014 08:01:06 - Revision: 2.1

Microsoft Word 2000 Standard Edition, Microsoft Word 97 Standard Edition

  • kbnosurvey kbarchive kbfaq kbmerge kbprb kbualink97 KB142340