Mail merge - Access query results in Word

hazelsusan · 08-01-2008, 12:56 PM

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** · 08-02-2008, 04:59 AM

Hi Susan,

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

hazelsusan · 08-04-2008, 01:58 AM

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** · 08-05-2008, 03:51 AM

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 · 08-05-2008, 02:49 PM

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.

08-01-2008, 12:56 PM	#1 (permalink)
hazelsusan Registered User Join Date: Aug 2008 Posts: 3 OS: Vista	Mail merge - Access query results in Word 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?

08-02-2008, 04:59 AM	#2 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 563 OS: Vista	Re: Mail merge - Access query results in Word Hi Susan, using 'mm' in a formatting picture switch denotes 'minutes', not 'months'. Try using: \@ "dd/MM/yy" __________________ Cheers macropod (MS MVP -Word)

08-04-2008, 01:58 AM	#3 (permalink)
hazelsusan Registered User Join Date: Aug 2008 Posts: 3 OS: Vista	Re: Mail merge - Access query results in Word 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.

08-05-2008, 03:51 AM	#4 (permalink)
macropod TSF Enthusiast Join Date: Apr 2008 Location: Australia Posts: 563 OS: Vista	Re: Mail merge - Access query results in Word 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. __________________ Cheers macropod (MS MVP -Word)

08-05-2008, 02:49 PM	#5 (permalink)
hazelsusan Registered User Join Date: Aug 2008 Posts: 3 OS: Vista	Re: Mail merge - Access query results in Word 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.