Mail Merge with Access 2003 (Office 2003)

[cancan]

I have word documents created containing merge fields but when I go to merge from my access database query the dates (required merge fields) appear as USA style. I have tried changing the dates in both my table and my form to long, medium and short but they still appear as 4/7/69 instead of 7 Apr 69. (They appear correct in the query). <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

[HansV]

Right-click one of the date merge fields in the Word document and select Toggle Field Codes from the popup menu. You will see something like

{ MERGEFIELD TheDate }

or

{ MERGEFIELD TheDate * MERGEFORMAT }

Change it to

{ MERGEFIELD TheDate @ "d MMM yy" }

The @ switch allows you to format the date; the string after it is the format code. Right-click and select Toggle Field Codes again to see the field results instead of field codes. Press F9 to update.

Alternatively, you can put the date as a formatted text field in the database query:

FormattedDate: Format([TheDate],"d MMM yy")

You would have to change the merge fields in the Word document.

[cancan]

Thanks a lot for that it worked brilliantly. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> Incidentally has anyone else experienced this in Office 2003 as I have tried merging dates into brand new templates from a new Access database and they automatically revert into USA format even though I format them in the Access table?

[HansV]

I don't have Office 2003, but you might experiment with the following:
- In Word, select Tools | Options..., General tab.
- Tick the check box labeled "Confirm conversion at Open".
- Click OK.
- Revisit the Mail Merge Wizard and select the Access database anew.
- You should get a choice of methods to connect to the Access database: OLE DB (default), DDE, ODBC.
- Perhaps if you select DDE, the format of the dates will be transferred to Word.
Caveats:
- In my experience, mail merge through DDE is slow.
- If the database has an application title that does not begin with "Microsoft Access", Word will keep on opening new instances of the database.

[johnhutchison]

I just tested this with my copy of Access 2003 and Word 2003 and didn't have this problem.

I formatted the date field in Access as dd/mm/yyyy and the dates appeared in exactly this format in Word. I did the merge with DDE.


But when I went into the Regional Settings Control Panel and reset my language to English (United States) and performed the merge again the dates came out in US style.