Solved

Date format problem - US Dates in mail merge!

Posted on 2006-11-23
10
5,845 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Outlook Free & Paid Tools
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now