Solved

Date format problem - US Dates in mail merge!

Posted on 2006-11-23
10
5,864 Views
Last Modified: 2008-06-03
I recently had a problem with all dates coming through to my mail merge template being in the American date format - i.e. MM/DD/YYYY.

With some help from the experts ( http://www.experts-exchange.com/Applications/Q_22050089.html ), I thought I had the problem nailed.

However....

I was wrong.

Now, when I do a mail merge, if the date that comes through could potentially be valid in both formats - for example 06/11/2006 is a valid UK date (6th November 2006) and a valid US date (11th June 2006) - the mail merge chooses the american option every time. To be pedantic, passing in 06/11/2006 to the mail merge results in 11/06/2006 appearing in the results.

If the date is only valid in the uk format, e.g. 23/6/2006, then it will show correctly as 23/6/2006.

This is extremely irritating, as you can imagine, how can I tell Word that the date format is ALWAYS the UK format! As you can see from the above question, I have already tried all of the obvious Office and Windows date settings.

Thanks,

Phil
0
Comment
Question by:TeamLink
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18004711
In general, in the later versions of Word, the underlying date data is passed as a (double) number. It can then be formatted as you require.

We probably need to know which application holds your original data, and how is it formatted there.
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 18005887
My guess is that not all your dates in your data source file are being seen as dates by the source application (Excel, Access?). My guess is that some of them are being seen as text. To find out, format the entire column as ddd or something. See if it returns an error or a day of the week. If I type 23/6/2006 into a cell and format it as a day of the week, it still says 23/6/2006. That means my system doesn't see it as a date.
0
 

Author Comment

by:TeamLink
ID: 18018277
The original data is in MSSQL where it is definitely held as a date.

It is passed into Word via an active X application, and a dbase driver. Does that help?

Phil
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18018978
I just checked your prior question, expecting to see that you were advised to use the date format switch. I can't see any mention of it.


Show the field codes with Alt+F9 toggle

This will change the view of merge fields from << MyDate>> to { MERGEFIELD "MyDate" }
Add the switch after the field name to get
 { MERGEFIELD "MyDate" \@ "dd/MM/yyyy" }

You can then hide the field codes again with Alt+F9
0
 

Author Comment

by:TeamLink
ID: 18036092
From the accepted answer on that question:

"If you Alt and F9 you will see the MergeFormat Field on your Word document. If you type \@"dd/MM/yyyy" at the end of the field the date is displayed in that format. NB type as seen i.e with quote marks and the month format must be in upper case for it to work."

Isn't that the same as you have just suggested??

Thanks,

Phil
0
 

Author Comment

by:TeamLink
ID: 18036936
just to give a bit more info and answer perhaps some of Dreamboats questions - as i said, the data is in MSSQL. I have checked that it is formatted correctly there and it is, and I have also checked that the fields are date fields, and they are!
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18037722
Yes, sorry.  I don't know how I missed seeing it. I was trying to avoid putting the problem out of Word's court.

I have experimented with the effect of that switch on text and it does the opposite of what you find, in that it swaps day and month dates if the first digit pair is greater that 12 while the second is not, but does nothing otherwise.

I wonder if there is something wrong with that document, or your Word installation, especially since you once thought that it worked OK.  Do you get the same effect with other documents or on other computers?

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18038237
Got that bit wrong too.

I have experimented with the effect of that switch on text and it does the opposite of what you find, in that it swaps day and month dates if the *second* digit pair is greater that 12 while the *first* is not, but does nothing otherwise.
0
 

Author Comment

by:TeamLink
ID: 18084407
GrahamSkan - thanks for your effort so far, it is appreciated, but I'm not sure if you are suggesting anything now that I can try, or just giving me more information?

Thanks,

Phil
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 175 total points
ID: 18087124
When connecting to an Excel or Access source, a date field is delivered in the format M/d/yyyy. This is what is expected by the date switch.

To help in diagnosis, this macro will display the received data for the first four records of the activedocument's datasource in a table of a new document. The field names are in the first column.

Sub CheckFields()
    Dim dfld As MailMergeDataField
    Dim docA As Document
    Dim doc As Document
    Dim tbl As Table
    Dim r As Integer
    Dim c As Integer
    Set docA = ActiveDocument
    Set doc = Documents.Add
    Set tbl = doc.Tables.Add(doc.Range, docA.MailMerge.DataSource.DataFields.Count, 5)
    For Each dfld In docA.MailMerge.DataSource.DataFields
        r = r + 1
        tbl.Cell(r, 1).Range.Text = dfld.Name
    Next dfld
    docA.MailMerge.DataSource.ActiveRecord = wdFirstRecord
    DoEvents
    For c = 2 To 5
        r = 0
        For Each dfld In docA.MailMerge.DataSource.DataFields
            DoEvents
            r = r + 1
            tbl.Cell(r, c).Range.Text = dfld.Value
        Next dfld
        docA.MailMerge.DataSource.ActiveRecord = wdNextRecord
    Next c
End Sub
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question