Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
I have created an excel database, which includes 1 column with various
numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
You'll make things much simpler for yourself if you create separate columns
for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
That would have been my preference too, however, I got this database, and
short of re-inputting it, can you think of a way around this? "Graham Mayor" wrote: You'll make things much simpler for yourself if you create separate columns for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
Still much easier to do it in the data source. You should not need to
re-enter everything that is already there. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Anthony J. B." wrote in message ... That would have been my preference too, however, I got this database, and short of re-inputting it, can you think of a way around this? "Graham Mayor" wrote: You'll make things much simpler for yourself if you create separate columns for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
Have you tried
a. use Tools|Customize to enable the mailmerge toolbar (I'm assuming you're using Word 2002/3) b. click Mail Merge Recipients (3rd icon on the toolbar) c. click on the drop-down in the header of the column you want to filter - let's say the field is called "myfield" d. select "(Advanced...)", the Filter Records e. select myfield in the drop-down list of fields f. select Contains in the "Comparison" g. select the number you want to compare with for the current merge in "Compare To" Your message suggests that you may have tried that, but please confirm/deny etc. Here, that results in an SQL query along the following lines SELECT * FROM `Sheet1$` WHERE `myfield` LIKE '%1%' and the filter does appear to work. (if that doesn't mean anything to you, I'll explain) Peter Jamieson "Anthony J. B." wrote in message ... That would have been my preference too, however, I got this database, and short of re-inputting it, can you think of a way around this? "Graham Mayor" wrote: You'll make things much simpler for yourself if you create separate columns for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
Thank you for this. I'm using Word 2000, so you know of anyway using this
version that I can get it to work. The database file is in Excel 2000 as well. Can I filter it through there better do you think? Awesome answers from everyone. Thank you so much. "Peter Jamieson" wrote: Have you tried a. use Tools|Customize to enable the mailmerge toolbar (I'm assuming you're using Word 2002/3) b. click Mail Merge Recipients (3rd icon on the toolbar) c. click on the drop-down in the header of the column you want to filter - let's say the field is called "myfield" d. select "(Advanced...)", the Filter Records e. select myfield in the drop-down list of fields f. select Contains in the "Comparison" g. select the number you want to compare with for the current merge in "Compare To" Your message suggests that you may have tried that, but please confirm/deny etc. Here, that results in an SQL query along the following lines SELECT * FROM `Sheet1$` WHERE `myfield` LIKE '%1%' and the filter does appear to work. (if that doesn't mean anything to you, I'll explain) Peter Jamieson "Anthony J. B." wrote in message ... That would have been my preference too, however, I got this database, and short of re-inputting it, can you think of a way around this? "Graham Mayor" wrote: You'll make things much simpler for yourself if you create separate columns for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
Unfortunately, the "Contains" option does not exist in Word 2000 as you
probably discovered, so to do the same thing you would have to issue the SQL using VBA and an OpenDataSource command, and I think you also have to connect using ODBC, which must be installed on your system. e.g. something like: Sub OpenExcelDS() Dim strExcelPath As String Dim strExcelDSN As String Dim strConnection As String ' The following is the standard DSN name but change it if yours is different strExcelDSN="Excel Files" ' Substitute the pathname of your file here strExcelPath="c:\myexcelfiles\myxls.xls" strConnection = strExcelDSN & ";DBQ=" & strExcelPath & ";DriverID=790;" ' Substitute the field names etc. that you need. ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:=strConnection, _ SQLStatement:="SELECT * FROM `Sheet1$` WHERE myfield like '%'2%" End Sub Peter Jamieson "Anthony J. B." wrote in message ... Thank you for this. I'm using Word 2000, so you know of anyway using this version that I can get it to work. The database file is in Excel 2000 as well. Can I filter it through there better do you think? Awesome answers from everyone. Thank you so much. "Peter Jamieson" wrote: Have you tried a. use Tools|Customize to enable the mailmerge toolbar (I'm assuming you're using Word 2002/3) b. click Mail Merge Recipients (3rd icon on the toolbar) c. click on the drop-down in the header of the column you want to filter - let's say the field is called "myfield" d. select "(Advanced...)", the Filter Records e. select myfield in the drop-down list of fields f. select Contains in the "Comparison" g. select the number you want to compare with for the current merge in "Compare To" Your message suggests that you may have tried that, but please confirm/deny etc. Here, that results in an SQL query along the following lines SELECT * FROM `Sheet1$` WHERE `myfield` LIKE '%1%' and the filter does appear to work. (if that doesn't mean anything to you, I'll explain) Peter Jamieson "Anthony J. B." wrote in message ... That would have been my preference too, however, I got this database, and short of re-inputting it, can you think of a way around this? "Graham Mayor" wrote: You'll make things much simpler for yourself if you create separate columns for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
Peter, thank you sooooo much for your help. This was invaluable.
Fortunately my client who I am doing this work for, had Word 2002, so I spent the afternoon with them at their office, and your instructions WORKED LIKE A CHARM. We spent a couple of hours extracting information from the database and she was very pleased. This makes me consider an upgrade at my end. Your generous response was very much appreciated. THANK YOU, THANK YOU. "Peter Jamieson" wrote: Unfortunately, the "Contains" option does not exist in Word 2000 as you probably discovered, so to do the same thing you would have to issue the SQL using VBA and an OpenDataSource command, and I think you also have to connect using ODBC, which must be installed on your system. e.g. something like: Sub OpenExcelDS() Dim strExcelPath As String Dim strExcelDSN As String Dim strConnection As String ' The following is the standard DSN name but change it if yours is different strExcelDSN="Excel Files" ' Substitute the pathname of your file here strExcelPath="c:\myexcelfiles\myxls.xls" strConnection = strExcelDSN & ";DBQ=" & strExcelPath & ";DriverID=790;" ' Substitute the field names etc. that you need. ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:=strConnection, _ SQLStatement:="SELECT * FROM `Sheet1$` WHERE myfield like '%'2%" End Sub Peter Jamieson "Anthony J. B." wrote in message ... Thank you for this. I'm using Word 2000, so you know of anyway using this version that I can get it to work. The database file is in Excel 2000 as well. Can I filter it through there better do you think? Awesome answers from everyone. Thank you so much. "Peter Jamieson" wrote: Have you tried a. use Tools|Customize to enable the mailmerge toolbar (I'm assuming you're using Word 2002/3) b. click Mail Merge Recipients (3rd icon on the toolbar) c. click on the drop-down in the header of the column you want to filter - let's say the field is called "myfield" d. select "(Advanced...)", the Filter Records e. select myfield in the drop-down list of fields f. select Contains in the "Comparison" g. select the number you want to compare with for the current merge in "Compare To" Your message suggests that you may have tried that, but please confirm/deny etc. Here, that results in an SQL query along the following lines SELECT * FROM `Sheet1$` WHERE `myfield` LIKE '%1%' and the filter does appear to work. (if that doesn't mean anything to you, I'll explain) Peter Jamieson "Anthony J. B." wrote in message ... That would have been my preference too, however, I got this database, and short of re-inputting it, can you think of a way around this? "Graham Mayor" wrote: You'll make things much simpler for yourself if you create separate columns for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merging/database management
Glad it all worked and thanks for the feedback.
Peter Jamieson "Anthony J. B." wrote in message ... Peter, thank you sooooo much for your help. This was invaluable. Fortunately my client who I am doing this work for, had Word 2002, so I spent the afternoon with them at their office, and your instructions WORKED LIKE A CHARM. We spent a couple of hours extracting information from the database and she was very pleased. This makes me consider an upgrade at my end. Your generous response was very much appreciated. THANK YOU, THANK YOU. "Peter Jamieson" wrote: Unfortunately, the "Contains" option does not exist in Word 2000 as you probably discovered, so to do the same thing you would have to issue the SQL using VBA and an OpenDataSource command, and I think you also have to connect using ODBC, which must be installed on your system. e.g. something like: Sub OpenExcelDS() Dim strExcelPath As String Dim strExcelDSN As String Dim strConnection As String ' The following is the standard DSN name but change it if yours is different strExcelDSN="Excel Files" ' Substitute the pathname of your file here strExcelPath="c:\myexcelfiles\myxls.xls" strConnection = strExcelDSN & ";DBQ=" & strExcelPath & ";DriverID=790;" ' Substitute the field names etc. that you need. ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:=strConnection, _ SQLStatement:="SELECT * FROM `Sheet1$` WHERE myfield like '%'2%" End Sub Peter Jamieson "Anthony J. B." wrote in message ... Thank you for this. I'm using Word 2000, so you know of anyway using this version that I can get it to work. The database file is in Excel 2000 as well. Can I filter it through there better do you think? Awesome answers from everyone. Thank you so much. "Peter Jamieson" wrote: Have you tried a. use Tools|Customize to enable the mailmerge toolbar (I'm assuming you're using Word 2002/3) b. click Mail Merge Recipients (3rd icon on the toolbar) c. click on the drop-down in the header of the column you want to filter - let's say the field is called "myfield" d. select "(Advanced...)", the Filter Records e. select myfield in the drop-down list of fields f. select Contains in the "Comparison" g. select the number you want to compare with for the current merge in "Compare To" Your message suggests that you may have tried that, but please confirm/deny etc. Here, that results in an SQL query along the following lines SELECT * FROM `Sheet1$` WHERE `myfield` LIKE '%1%' and the filter does appear to work. (if that doesn't mean anything to you, I'll explain) Peter Jamieson "Anthony J. B." wrote in message ... That would have been my preference too, however, I got this database, and short of re-inputting it, can you think of a way around this? "Graham Mayor" wrote: You'll make things much simpler for yourself if you create separate columns for each comment number. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Anthony J. B. wrote: I have created an excel database, which includes 1 column with various numbers representings comments that were made. Each records has an entry in this column which is either a single digit number, or a combination of single digit numbers, separated by a comma, ie., 1 / 1,2,3 / 1,4,2. From word what I am trying to do is filter the records out from excell so that only the records containing, for example, 2, either in combination with other numbers, or singularly, only those records will merge with excell. I have been trying to do this using the "Insert Word Field" option as sort of a filter, but it is not working. I'm not sure which one I probably should use, if there is one. I have also tried using the "merge query to filter" but that doesn't seem to work. Both options give me the records containing the singluar 1, but it doesn't pull them out of a group. Help |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Doc management, workflow or job tracking? | Microsoft Word Help | |||
How can I view my contact management from any computer? | Microsoft Word Help | |||
rights management in 2003 and previous versions compatibility.- | Microsoft Word Help | |||
Style sheet creation and management utility | Microsoft Word Help | |||
management letter regarding audit of inventory | Microsoft Word Help |