Hi, I'm new to mailmerging and very confused.



I have produced a mail merge document which works fine except that I have just added a new date field into the document using and existing field from the database (Excel)

The date will only appear in the document as a 5 digit number which I assume is a day count??

How do I get the proper English date format to appear??

Regards

IIRC, this would only occur if the column has a mix of data types in the column (e.g. text or numbers, plus the dates). This includes text strings that are formatted to look like dates. If practicable, you should ensure there are only dates in that column. If that can't be done, the serial #s can be converted to dates, but that is liable to convert other numbers also:

{QUOTE
{SET jd{=2415019+{=INT({MERGEFIELD MyDate})}}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field construction are all required, but the line breaks aren't - they're just to make the field code easier to read.

For a macro to convert the above field text strings to working field codes, see: http://www.gmayor.com/export_field.htm#TextToField

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

I'm afraid all that was way beyond my small brain.

The cell in the excel file is formatted as a date.

The date is entered as follows: 17/5/14 which displays as 17 May 2014

This still only displays as the 5 digit number in the Word document.

Cheers,

Andy

Is there anything other than dates in this column? For example, if you change the number format to, say, General, do all the dates change to 5-digit numbers? Is there anything else in the same column that isn't a date? For example, and ordinary number or some text?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,
As far as I can see, the only thing in the column are dates and some blank cells.

Cheers,

Andy

Are there blanks in the first 8 rows?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

Yes, I may have just realised the problem??
Row 1 is a title (not a date!)
Rows 2 - 9 are blank, from 10 on there is a date in each cell.

It's a good job you know what to look for?

Cheers,

Andy

That, then is the cause of the problem. To determine how to represent the output data, the OLE DB provider first 8 records to determine the data type. In this case, they don't contain dates. If you can reorder the data so you have just dates there, they'll merge correctly. I assume your title (Row 1) is the header row - don't move that!

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,
I just put dummy dates in the first 9 rows and guess what, you are a genius!!

One last question before I let you escape, how do I get the English date format in the word doc. It is displayed correctly in the database but changes to the US format in Word.

7th April 2014 becomes 4/7/2014

Cheers,

Andy

The OLE DB server used for mailmerges basically imports just the data, with little attention to its formatting. However, Word has extensive support for managing the formats. See: https://www.msofficeforums.com/mail-...ps-tricks.html

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]