Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date Format Word 2002
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date Format Word 2002
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date Format Word 2002
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date Format Word 2002
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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Date Format Word 2002
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
take yet another lesson from wordperfect "reveal codes" | Microsoft Word Help | |||
Word should allow to 'divide' page by 3 or 4, not just 2 | Page Layout | |||
Converting Word Perfect forms to Word forms | Microsoft Word Help | |||
I can't find a free, word 2002 download | New Users | |||
Envelope Address | New Users |