Solved

Date format problem - US Dates in mail merge!

Posted on 2006-11-23
10
5,870 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

628 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