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

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

"Chris Stammers" wrote in message
...
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

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

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

Peter Jamieson

"Peter Jamieson" wrote in message
...
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

"Chris Stammers" wrote in
message ...
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,

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" wrote:

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

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

Peter Jamieson

"Peter Jamieson" wrote in message
...
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

"Chris Stammers" wrote in
message ...
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

If you mean this:

SELECT mid(d,8,4) & '-' & mid(d,2,2) & '-' & mid(d,5,2) & '
00:00:00' as `mydate`

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

"Chris Stammers" wrote in message
...
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" wrote:

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

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

Peter Jamieson

"Peter Jamieson" wrote in message
...
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

"Chris Stammers" wrote in
message ...
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