Merge Date Error with change in calendar year

[Frank Santoro]

I have a word 2003 merge document that pulls data from an excel 2003
sheet. The specific field I am pulling from (AFRMonth) has the date
12/01/2005. In word, I have the field formatted as follows:

{MERGEFIELD "AFRMonth" \@ "MMMM yyyy"}

so that it should show as "December 2005" in the document. However,
the formatted field is showing as "December 2006" instead. If I change
the system date to 2005, the field shows properly as "December 2005".
If I remove the formatting switches, the field shows "Dec-05"
regardless of the system date.

Any suggestions?

 

[Graham Mayor]

I can't reproduce this error using the information supplied.

In a US based system, if the Excel field contains 12/01/2005 then the merge
without the switch should show the same (or depending on how you connect to
the data, it could show
12/01/2005 00:00:00. )

With the switch, it would show
December 2005.

How are you connecting the merge document to Excel?

How is the field formatted in Excel?

Is the field a derived field in Excel, if so, what's the calculation?

It sounds as though the Excel date is derived from some calculation which
uses the system date.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Frank Santoro]

Well, I played around some more, and the error seems to reproduce only
when certain date formats are used in Excel. I've not done an
exhaustive test but under the conditions I posted in my first message:

Word shows "December 2006" if
- system date is set to 2006
- date format in excel is "mmm-yy" or "mmmm-yy" (the excel format
throws this [$-409] bit in front of some of the date formats under the
custom format list, including these two formats, but this seems to be
only a coincidence

I changed the date format in the spreadsheet to m/d/yyyy and Word shows
"December 2005", as it should.

In answer to some of your other questions:
- The connection is being made through the word merge SQL command
SELECT * FROM...
- The field is not derived in Excel; it is entered just as a date

I'd be curious whether or not anyone can reproduce the error given this
information. If not, I'd be happy to upload the word and excel files
somewhere for folks to test.

Frank

 

[Cindy M -WordMVP-]

<[email protected]>
<[email protected]>
Newsgroups: microsoft.public.word.mailmerge.fields
NNTP-Posting-Host: 206.93.202.62.cust.bluewin.ch 62.202.93.206
Path: number1.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed.hal-mli.net!feeder1.hal-mli.net!news.alt.net!msrtrans!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
Lines: 1
Xref: number1.nntp.dca.giganews.com microsoft.public.word.mailmerge.fields:58984

Hi Frank,

I think what's happening here is that any combination of just month and
year, with a year number of 12 or less, is ambiguous. Try typing Dec-05
into an EXCEL cell, with this number format. I get Dec-06, the same thing
you're describing with Word.

This is one reason why Graham asked which connection protocol you're
using for the mail merge. An SQL string is not a connection, just a
filter Possible connection protocols would be: OLE DB, ODBC, DDE or
the spreadsheet converter.

Well, I played around some more, and the error seems to reproduce only
when certain date formats are used in Excel. I've not done an
exhaustive test but under the conditions I posted in my first message:

Word shows "December 2006" if
- system date is set to 2006
- date format in excel is "mmm-yy" or "mmmm-yy" (the excel format
throws this [$-409] bit in front of some of the date formats under the
custom format list, including these two formats, but this seems to be
only a coincidence

I changed the date format in the spreadsheet to m/d/yyyy and Word shows
"December 2005", as it should.

In answer to some of your other questions:
- The connection is being made through the word merge SQL command
SELECT * FROM...
- The field is not derived in Excel; it is entered just as a date

I'd be curious whether or not anyone can reproduce the error given this
information. If not, I'd be happy to upload the word and excel files
somewhere for folks to test.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Frank Santoro]

Well, the data as typed in excel was "12/01/2005". When I had the
excel cell formatted as mmm-yy (i.e., displaying Dec-05), I got the
error in Word (i.e., the merge field with switches displayed "December
2006"). When I changed the format in the excel cell to mm/dd/yyyy,
there merged data in Word displayed "December 2005".

Now as to connection protocol, I have no idea what you mean. How do I
find out? All I know is I am using the word merge function.

 

[Cindy M -WordMVP-]

Hi Frank,

Well, the data as typed in excel was "12/01/2005". When I had the
excel cell formatted as mmm-yy (i.e., displaying Dec-05), I got the
error in Word (i.e., the merge field with switches displayed "December
2006"). When I changed the format in the excel cell to mm/dd/yyyy,
there merged data in Word displayed "December 2005".

Now as to connection protocol, I have no idea what you mean. How do I
find out? All I know is I am using the word merge function.

OK, if you didn't choose anything, and this is Word 2003, then the
connection method is OLE DB (unless this document + the connection to
the data source originated in an earlier version of Word?). Can you see
this if you start with a new document and
1. Tools/Options/General, activate "Confirm conversions on open"

2. Connect to the worksheet.

3. You'll get a dialog box with a list of connection methods. Which one
is selected by default? Go through and test with each, keeping a record
of the result you get in each case. (For ODBC you'll probably need to
click "Options" and activate "System tables" in order to get a list of
worksheet names) What kinds of results do you get?

Do you see this only with Dec. 1? Or does it also happen with any other
day (up to and including 12)?

You see, Excel saves date information as long integers (number of days
since Dec. 31, 1901 or something like that). Most Office applications
know how to take these integers and display them as dates, using the
formatting pictures. But the applications can get confused if you start
with an ambiguous date (12-05, both numbers could be day, month or
year). In such a case, they'll tend to say OK, this is the day and
month, I'll just hang the current year on it. This appears to be what
you're seeing, but I have to know exactly what circumstances in order to
test on my installation.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail