Date Format Word 2002

[Chris Stammers]

Hello,

I am using Word 200 and I am trying to create a mailmerge letter which has
to use a pre-populated date in an Excel csv file. The problem is, that the
data source has the date formatted as mm/dd/yyyy hh:mm:ss. I need the date to
show as dd MMMM yyyy and the usual formatting picture switch \ @ "dd MMMM
yyyy" didn't work; it just brought through a '17'. I am not trying to put in
today's date, I am trying to input the date in the field. There are also
blank rows between each record. I have tried the 'SKIP IF {mergefield}
ISBLANK' function and that didn't work either. Can anyone help, please?

Thanks,
Chris

 

[Peter Jamieson]

First, I would check all the connection methods (check Word
Tools|Options|General|Confirm conversion at open, reconnect to the data
source, and select text file, OLEDB, Delimited Text File via ODBC.

Here connecting via ODBC works with OLEDB but the ODBC driver can really
mess around with anything it thinks is a date. Also, the behaviour will
probably differ depending on whether the "blank records" are totally blank,
or contain the expected number of comma delimiters. You also really need to
check that the month and day for dates such as 06/07/2006 and 07/06/2006 are
the way around they should be.

You may find that the only way to make a successful ODBC connection is to
use VBA, e.g. the following works OK here using a file with delimiters in
the empty records (but I'm using Word 2003)

Sub Connect2Csv()
Dim strConnection As String
Dim strCSVFolder As String
Dim strCSVName As String
Dim strQuery As String

' Substitute the folder you need
strCSVFolder = "c:\a"
' and the file name you need
strCSVName = "mydates.csv"
strConnection = "DSN=Delimited Text Files;DBQ=" & strCSVFolder &
";DriverId=27;FIL=text;"
' see notes below
strQuery = "SELECT * FROM `" & strCSVName & "` WHERE myfield is not null"
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:=strConnection, _
SQLStatement:=strQuery, _
SubType:=wdMergeSubTypeWord2000
End Sub

In the SQL statement you need to substitute a field that is blank in the
empty records for "myfield". You may also find that you need myfield <> ''
instead of myfield is not null, depending on what ODBC thinks the field type
is.

If using ODBC forces word to recognise the date, you are probably then OK.
Otherwise, you can try piecing a date together yourself, e.g.

strQuery = "SELECT mid(d,8,4) & '-' & mid(d,2,2) & '-' & mid(d,5,2) & '
00:00:00' as `mydate` FROM `" & strCSVName & "` WHERE myfield is not null"

where you substitute the name of your date field instead of "d". However,
this is tricky because if ODBC has already recognised "d" as a date it may
switch around the day and month before you even have a chance to pick the
individual bits out of it.

Peter Jamieson

 

[Peter Jamieson]

Oh yes, if you haven't used Word VBA before, see

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Peter Jamieson

 

[Chris Stammers]

Peter,

Thanks very much. I don't fully understand your formulae here however I will
sit with it and have a go. Thanks again for your help.

Regards,
Chris

 

[Peter Jamieson]

If you mean this:

I am starting with a date in mm/dd/yyyy hh:mm;ss format as you specified,
and turning it into

yyyy-mm-dd 00:00:00

format, which Word should be able to work with as it's the standard ?ISO
date format or something like that.

Peter Jamieson