Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Microsoft Access 2002 mail merging to Word 2002

Aug 3, 2005 8:57PM PDT

I am having a problem with Microsoft Access 2002 mail merging to Word 2002. I am storing a date field in my database in the format "dd MMMM yyyy". When I type in a my date 10/06/05 it displays correctly as 10 June 2005, the problem is when I use that field in my Word mail merge as { MERGEFIELD "strTest_Date" \@ "dd MMMM yyyy" } the date swaps around and displays in the Word doc as 6 October 2005. Any ideas why? I am totally stumped!

Many thanks!

Discussion is locked

- Collapse -
Re: dates
Aug 3, 2005 10:12PM PDT

A date in Access is just a number, and the display format defines how it's displayed. So in 'reality' the number in the field should be 38513 (that's June 10, 2005 as you can check in Excel by changing the format of the cell). While October 6, 2005 is 38631.

I would start with checking what's really in the field by exporting the table to Excel (read through http://reviews.cnet.com/5208-6129-0.html?forumID=29&threadID=117591&messageID=1335183 if necessary) and check the contents to see if Word is right or Access.

I think this is some interplay with the date format defined in Control Panel>International and the field settings or option settings in Access and Word. If you say 10/6 is 6 june correctly, I assume you live a part of the world where that's correct (not the USA) like here (Holland) and that the Windows International settings is set accordingly. I don't have Office 2002 here (only XP) and don't have the time at the moment to try it here. Maybe later on.

Can you tell your OS, your country and your date display settings in Windows International? That might help. And please post the results of your little experiment by exporting the table to Excel.

Kees

- Collapse -
Microsoft Access 2002 mail merging
Aug 3, 2005 11:51PM PDT

I did export the table from access yesterday whilst trying to solve the problem, and Access is correct. Whilst playing about with it further I have now solved the problem but the fact that the problem occured is a worry. I think it must have been a bug because when I merged a record containing a date 23 May 2005 it merged fine! I had also tried entering in 6 Oct 2005 into my Access database to see if it swapped that date around and it didn't! After all that, I entered 10 June 2005 again and this time it displayed it correctly. The only thing I can think is that it is one of those strange Windows Bugs!

I am running Windows XP and live in the UK, in Regional Settings my location is set to English (United Kingdom).

- Collapse -
(NT) (NT) Glad to see it's solved. Hope it stays that way.
Aug 4, 2005 2:01AM PDT
- Collapse -
Spoke too soon!
Aug 4, 2005 2:44AM PDT

It does not seem to be working again, I think I hit a bug! I have decided that the problem must be with word thinking that its date should be stored with an American format rather than my UK settings. However, when I go to Insert->Date and Time it clearly shows the language as English UK and calender type Western. I just can't think of any other reason for this problem!

- Collapse -
This is how I did it in an application.
Aug 4, 2005 6:45PM PDT

I wouldn't even speak of a work-around for your problem, because it was the logical way for me, right from the start.

- In Access, use a query to export the data. If fact, I always use a query to export data, never directly from a table, because of the added control you've got.
- In the query, format the date the way you like it, with the format-function. You might have to set the length in the export definition, if you export to a file, but I don't think that's necessary for a mail-merge (can't check it right now). Something like format(mydate,"d mmmm yyyy") or whatever you like.
- The field is now a simple character field, like name or address, and that's exactly the way it should be merged in Word.

This way, all 'difficult' things are done in Access. Word is only used to process text. And that's a sound way of doing things.

Hope this helps.


Kees

- Collapse -
Reply to: This is how I did it in an application
Aug 4, 2005 8:07PM PDT

I think I am loosing you Kees. I am merging to word from a query in my database, but don't want to export to another application before merging to word as it will make the whole thing messy and not very elegant. If you are suggesting that I somehow get the database query to convert the date into a text type format, then if you could explain how.

If not, then I think I have tried what you are suggesting and am still getting the problem. I am merging from a query and the date is formatted in Access how I would like it to appear in Word, but when it appears in word, the date is being taken as American format.

Thanks for your patience!

- Collapse -
Explanation.
Aug 4, 2005 8:34PM PDT

Fraggle,

You already use a query as a datasource, that's a very good start.

Let me suppose the datefield is called mydate. Then at the moment you've got the field mydate in the query, like select myname, mydate from mytable (writing it in SQL, witch is equivalent to the GUI presentation)

Simply change the field in the query to fdate:format(mydate,'dd-mm-yyyy') (or any date format you like). The SQL now reads select myname, format(mydate,'dd-mm-yyyy') as fdate from mytable. Checking the result in Access is very easy by switching to data view from design view. You'll see myname and fdate both as text fields.
To put it differently: you put a calculated field (expression) in your query, not only database fields.

In Word you can now use the field fdate as a text field in your mailmerge, just as you use the field myname.

Give it a try and please tell if any problems arise. This surely is solvable.

Kees

- Collapse -
It worked!
Aug 5, 2005 2:33AM PDT

Thank you Kees!

Your solution worked! I would not have thought of it myself, and it is a pain that we have to go to these lengths because Microsoft does not seem up to the job, but at least for me, help was at hand in the form of you!

Thanks also for your patience and taking the time to explain to me the SQL behind what I was doing!

Fraggle!

- Collapse -
(NT) (NT) You're welcome.
Aug 5, 2005 4:00AM PDT