# Mail merge - Access query results in Word



## hazelsusan (Aug 1, 2008)

The problem is with a mail merge - data is from a query in Access (2002) merged with a document in Word (2002). The date in the data is in English format (dd/mm/yy) in the query and is changed to American format in Word (mm/dd/yy) - field codes (in Word) with an edit picture to swap around the day and month do not work because the month then shows in Word as zeroes. This problem shows with Office 2002 running under Vista but has also appeared with Office 2003 running under Windows XP. The computer settings correctly show that the date is set up for English settings. In this particular case one of the other columns of data from the query is picking up data from other fields in the record when viewed through Word - but is accurate when viewed as a query in Access. Is there a known problem with the Word-Access interface with 2002/2003? Any known solutions/workarounds?


----------



## macropod (Apr 11, 2008)

Hi Susan,

using 'mm' in a formatting picture switch denotes 'minutes', not 'months'. Try using:
\@ "dd/MM/yy"


----------



## hazelsusan (Aug 1, 2008)

Many thanks for your response. 
Sorry - I didn't express myself very clearly - the problem is that Word appears to be corrupting data from a query in Access. The date should show in English format in both the query and in the mail merge results - it has been changed to American format for some reason by Word itself (it looks OK when viewing query results in Access) and I don't know why. My attempts to reformat it in Word back to English format using field codes have had limited results. I can't find the option in Word where the date format is set - the computer settings themselves correctly show that the date format is English (which is what we want). The other problem is that another column (which also happens to be a date) shows random data from other fields (including items such as Post Code, Addresses) when, again, the results show correctly in the query in Access. It appears that Word has lost or corrupted formatting information when inputing the data from the query. I have asked the person who is experiencing these problems to reinstall Word - but as it is a problem I have seen elsewhere on a completely different system I would like to know if anyone else has had a similar problem? Many thanks for any info.


----------



## macropod (Apr 11, 2008)

Hi Susan,

There is no date format option within Word, itself. Word relies on your computer's regional settings, for which you should ensure that the region and both the short and long date formats are all correct.

Even so, with a date picture switch, you must use 'M', 'MM', 'MMM' or 'MMMM' not 'm' or 'mm' to indicate months.

As for the corrupted output, it sounds like either:
. the mailmerge document has become corrupted or,
. perhaps your query isn't outputting empty fields, with the result that fields being output to the Word document are somethimes being offset Ifiguratively to the left) from their correct position. I don't know enough about this to comment further, though.

Given that the same problem manifests on two or more systems, I doubt that reinstalling Word will resolve the issue - running 'Detect & Repair' is the most that should be needed.


----------



## hazelsusan (Aug 1, 2008)

Thanks - I'll have another look to see if there are any empty fields causing the problem. Using dd/MM/yy works fine - but since all computer settings are for UK English I still don't know why the date is being changed anyway. I am on holiday for a week now so won't be able to try anthing more just yet. Many thanks.


----------



## munna94 (Feb 16, 2010)

Hello hazelsusan,

I know it has been a while you posted this post but were you able to resolve this issue? I am having the same problem, here is my problem description:

I am trying to do a mail merge, everything seems to be good.

But, i have a date field in the access query, which is in this format June 19, 1994, this date field when merged to the word changes the format of the date in the word document, i mean to say that when i merge the access query to a word document the date field format i get is 06/19/1994, but the actual format in the access query is June 19, 1994.

Can anyone please help and feel free to ask me any questions if you have.

Thanks,
Munna


----------



## munna94 (Feb 16, 2010)

Guys, here is the solution:

Even if the query gives out the correct date format which you want, change the query in this way:

Actual Query: select joined_date,name,id from employee;

Change the above query to:

select format(joined_date,"mmm dd,yyyy") AS [joined_date],name,id from employee;

Now using the above changed query if you try merging to the word document, you would get it as it was in the query output.


Thanks,
Munna


----------



## macropod (Apr 11, 2008)

Hi Munna,

The simplest and most flexible solution, which works with any data source, is to add a formatting switch to the mergefield in Word. Amongst other things, this approach allows you to express the same field's results in different ways in different parts of the same report. AFAIK you can't do that by formatting the returned date in the query.

To apply a date format via a formatting picture switch in Word::
. select the mergefield;
. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include:
. \@ "dddd, d MMMM yyyy";
. \@ "ddd, d MMMM yyyy";
. \@ "d MMM yyyy";
. \@ "dd/MMM/yyyy";
. \@ "d-MM-yy";
Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months (lowercase 'm's are for minutes).
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.


----------

