| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
inserting the DATABASE field in a mailmerge with filtered reults
I have a Mailmerge document and I want to use the FIELD option (Insert,
Field on the toolbar) to display filtered results from a spreadsheet or access table. I am able to insert the DATABASE field but it shows all the rows the spreasheet( retail.xls). i was wondering if there is a way to filter the resutls so that ONLY the rows that have the same value as the PORTFILIO_ID wordfield are displayed. Any advice or info would be greatly appreciated |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
inserting the DATABASE field in a mailmerge with filtered reults
Use Alt-F9 to view the { DATABASE } field code. At the moment it
probably has something like \s "SELECT * FROM `Sheet1$`" or some such, near the end. Precisely what is there, and what you can do with it, depends on the version of Word and how you are connecting to the sheet. The basic idea is to add a WHERE clause like this \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" where the {} are the special field code braces that you can insert using ctrl-F9. That (or something similar) should be OK if the column is numeric. If Word thinks it's a text column, you would need something more like \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = '{ MERGEFIELD Portfolio_ID }'" It may need a bit more refinement to get it to work, but that's the starting point anyway. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: I have a Mailmerge document and I want to use the FIELD option (Insert, Field on the toolbar) to display filtered results from a spreadsheet or access table. I am able to insert the DATABASE field but it shows all the rows the spreasheet( retail.xls). i was wondering if there is a way to filter the resutls so that ONLY the rows that have the same value as the PORTFILIO_ID wordfield are displayed. Any advice or info would be greatly appreciated |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
inserting the DATABASE field in a mailmerge with filtered reul
Hi Peter,
Thanks for your advice, however, when i choose to insert a database field, all that i see (even after choosing the database) is {DATABASE \* MERGEFORMAT} I tried changing it to the code you suggested and nothing changed. BTW the name of the sheet & spreadsheet is "Retail_SUBMISSION" if that helps at all Thanks! "Peter Jamieson" wrote: Use Alt-F9 to view the { DATABASE } field code. At the moment it probably has something like \s "SELECT * FROM `Sheet1$`" or some such, near the end. Precisely what is there, and what you can do with it, depends on the version of Word and how you are connecting to the sheet. The basic idea is to add a WHERE clause like this \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" where the {} are the special field code braces that you can insert using ctrl-F9. That (or something similar) should be OK if the column is numeric. If Word thinks it's a text column, you would need something more like \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = '{ MERGEFIELD Portfolio_ID }'" It may need a bit more refinement to get it to work, but that's the starting point anyway. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: I have a Mailmerge document and I want to use the FIELD option (Insert, Field on the toolbar) to display filtered results from a spreadsheet or access table. I am able to insert the DATABASE field but it shows all the rows the spreasheet( retail.xls). i was wondering if there is a way to filter the resutls so that ONLY the rows that have the same value as the PORTFILIO_ID wordfield are displayed. Any advice or info would be greatly appreciated |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
inserting the DATABASE field in a mailmerge with filtered reul
If you just insert a DATABASE field using Insert-fields, that what
you'll get. But when you used Insert-Datadase to insert a DATABASE field there should have been a whole lot more than that. Which version of Word? Here's an example of a complete DATABASE field from Word 2003, using OLE DB to connect, as inserted by Word { DATABASE \d "C:\\myxlfiles\\retail.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\myxlfiles\\retail.xls;Mode=Read;Extende d Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB  atabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDB atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT * FROM `Retail_SUBMISSION$`" \h } You should be able to modify the last part of that to \s "SELECT * FROM `Retail_SUBMISSION$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" \h as I suggested. You should also be able to cut out either the entire \c parameter (all the connection string stuff) or at least most of it) if you like to have shorter field codes. You will get the data formatting that the OLE DB provider chooses to use. Generally speaking in later versions of Word, using a DDE connection to avoid that is not a good option, but since you can't do it the other way, using Word field formatting switches, the only other option is to use Jet SQL VBA functions in the SELECT string to format the values. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: Hi Peter, Thanks for your advice, however, when i choose to insert a database field, all that i see (even after choosing the database) is {DATABASE \* MERGEFORMAT} I tried changing it to the code you suggested and nothing changed. BTW the name of the sheet & spreadsheet is "Retail_SUBMISSION" if that helps at all Thanks! "Peter Jamieson" wrote: Use Alt-F9 to view the { DATABASE } field code. At the moment it probably has something like \s "SELECT * FROM `Sheet1$`" or some such, near the end. Precisely what is there, and what you can do with it, depends on the version of Word and how you are connecting to the sheet. The basic idea is to add a WHERE clause like this \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" where the {} are the special field code braces that you can insert using ctrl-F9. That (or something similar) should be OK if the column is numeric. If Word thinks it's a text column, you would need something more like \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = '{ MERGEFIELD Portfolio_ID }'" It may need a bit more refinement to get it to work, but that's the starting point anyway. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: I have a Mailmerge document and I want to use the FIELD option (Insert, Field on the toolbar) to display filtered results from a spreadsheet or access table. I am able to insert the DATABASE field but it shows all the rows the spreasheet( retail.xls). i was wondering if there is a way to filter the resutls so that ONLY the rows that have the same value as the PORTFILIO_ID wordfield are displayed. Any advice or info would be greatly appreciated |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
inserting the DATABASE field in a mailmerge with filtered reul
I am using Word 2003
When I initally insert the database i have the following {DATABASE \d "C:\\Status Mailer.mdb" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\Documents and Settings\\rc20540\\Desktop\\GCBC Mailer\\GCBC Status Mailer.mdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB atabase Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB atabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT `Portfolio ID`, `User Name`, `User` FROM `Wholesale_200908_submit`" \h} Then I do as advised and update to {DATABASE \d "C:\\Status Mailer.mdb" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\Documents and Settings\\rc20540\\Desktop\\GCBC Mailer\\GCBC Status Mailer.mdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB atabase Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB atabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT * FROM `Wholesale_200908_submit` WHERE 'Portfolio ID' = {MERGEFIELD "Portfolio_ID"} " \h} and i get And the result is "ERROR! Cannot Open Data source" "Peter Jamieson" wrote: If you just insert a DATABASE field using Insert-fields, that what you'll get. But when you used Insert-Datadase to insert a DATABASE field there should have been a whole lot more than that. Which version of Word? Here's an example of a complete DATABASE field from Word 2003, using OLE DB to connect, as inserted by Word { DATABASE \d "C:\\myxlfiles\\retail.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\myxlfiles\\retail.xls;Mode=Read;Extende d Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB atabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDB atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT * FROM `Retail_SUBMISSION$`" \h } You should be able to modify the last part of that to \s "SELECT * FROM `Retail_SUBMISSION$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" \h as I suggested. You should also be able to cut out either the entire \c parameter (all the connection string stuff) or at least most of it) if you like to have shorter field codes. You will get the data formatting that the OLE DB provider chooses to use. Generally speaking in later versions of Word, using a DDE connection to avoid that is not a good option, but since you can't do it the other way, using Word field formatting switches, the only other option is to use Jet SQL VBA functions in the SELECT string to format the values. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: Hi Peter, Thanks for your advice, however, when i choose to insert a database field, all that i see (even after choosing the database) is {DATABASE \* MERGEFORMAT} I tried changing it to the code you suggested and nothing changed. BTW the name of the sheet & spreadsheet is "Retail_SUBMISSION" if that helps at all Thanks! "Peter Jamieson" wrote: Use Alt-F9 to view the { DATABASE } field code. At the moment it probably has something like \s "SELECT * FROM `Sheet1$`" or some such, near the end. Precisely what is there, and what you can do with it, depends on the version of Word and how you are connecting to the sheet. The basic idea is to add a WHERE clause like this \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" where the {} are the special field code braces that you can insert using ctrl-F9. That (or something similar) should be OK if the column is numeric. If Word thinks it's a text column, you would need something more like \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = '{ MERGEFIELD Portfolio_ID }'" It may need a bit more refinement to get it to work, but that's the starting point anyway. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: I have a Mailmerge document and I want to use the FIELD option (Insert, Field on the toolbar) to display filtered results from a spreadsheet or access table. I am able to insert the DATABASE field but it shows all the rows the spreasheet( retail.xls). i was wondering if there is a way to filter the resutls so that ONLY the rows that have the same value as the PORTFILIO_ID wordfield are displayed. Any advice or info would be greatly appreciated |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
inserting the DATABASE field in a mailmerge with filtered reul
It's pretty difficult to debug these things from a distance, so I can
only make suggestions about what to try next, but a. I think you should be able to cut out the connection string. (I'll try to check that here...) b. is the database actually at c:\status_mailer.mdb or at the much longer path in the connection string? c. notice that there are two types of quotes: straight quotes ' ' and backquotes ``. Sometimes the syntax requires one, sometimes the other. As far as I know, with the version of Access you are using, it should be possible to replace all the backquotes by [] pairs, which is particularly handy if you do not have backquotes on your keyboard. d. So if it's c:\status_mailer.mdb, have a go with { DATABASE \d "C:\\Status Mailer.mdb" \s "SELECT * FROM [Wholesale_200908_submit] WHERE [Portfolio ID] = {MERGEFIELD "Portfolio_ID"}" \h} (if the Portfolio ID is a text field, surround that mergefield with single straight quotes: { DATABASE \d "C:\\Status Mailer.mdb" \s "SELECT * FROM [Wholesale_200908_submit] WHERE [Portfolio ID] = '{MERGEFIELD "Portfolio_ID"}' " \h} If that does not work, try using a real value for the portfolio ID first, e.g. if there is a value 1234 on the database, try { DATABASE \d "C:\\Status Mailer.mdb" \s "SELECT * FROM [Wholesale_200908_submit] WHERE [Portfolio ID] = 1234" \h} or { DATABASE \d "C:\\Status Mailer.mdb" \s "SELECT * FROM [Wholesale_200908_submit] WHERE [Portfolio ID] = '1234'" \h} depending on the data type. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: I am using Word 2003 When I initally insert the database i have the following {DATABASE \d "C:\\Status Mailer.mdb" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\Documents and Settings\\rc20540\\Desktop\\GCBC Mailer\\GCBC Status Mailer.mdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB atabase Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB atabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT `Portfolio ID`, `User Name`, `User` FROM `Wholesale_200908_submit`" \h} Then I do as advised and update to {DATABASE \d "C:\\Status Mailer.mdb" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\Documents and Settings\\rc20540\\Desktop\\GCBC Mailer\\GCBC Status Mailer.mdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB atabase Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB atabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT * FROM `Wholesale_200908_submit` WHERE 'Portfolio ID' = {MERGEFIELD "Portfolio_ID"} " \h} and i get And the result is "ERROR! Cannot Open Data source" "Peter Jamieson" wrote: If you just insert a DATABASE field using Insert-fields, that what you'll get. But when you used Insert-Datadase to insert a DATABASE field there should have been a whole lot more than that. Which version of Word? Here's an example of a complete DATABASE field from Word 2003, using OLE DB to connect, as inserted by Word { DATABASE \d "C:\\myxlfiles\\retail.xls" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=C:\\myxlfiles\\retail.xls;Mode=Read;Extende d Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB atabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDB atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT * FROM `Retail_SUBMISSION$`" \h } You should be able to modify the last part of that to \s "SELECT * FROM `Retail_SUBMISSION$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" \h as I suggested. You should also be able to cut out either the entire \c parameter (all the connection string stuff) or at least most of it) if you like to have shorter field codes. You will get the data formatting that the OLE DB provider chooses to use. Generally speaking in later versions of Word, using a DDE connection to avoid that is not a good option, but since you can't do it the other way, using Word field formatting switches, the only other option is to use Jet SQL VBA functions in the SELECT string to format the values. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: Hi Peter, Thanks for your advice, however, when i choose to insert a database field, all that i see (even after choosing the database) is {DATABASE \* MERGEFORMAT} I tried changing it to the code you suggested and nothing changed. BTW the name of the sheet & spreadsheet is "Retail_SUBMISSION" if that helps at all Thanks! "Peter Jamieson" wrote: Use Alt-F9 to view the { DATABASE } field code. At the moment it probably has something like \s "SELECT * FROM `Sheet1$`" or some such, near the end. Precisely what is there, and what you can do with it, depends on the version of Word and how you are connecting to the sheet. The basic idea is to add a WHERE clause like this \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = { MERGEFIELD Portfolio_ID }" where the {} are the special field code braces that you can insert using ctrl-F9. That (or something similar) should be OK if the column is numeric. If Word thinks it's a text column, you would need something more like \s "SELECT * FROM `Sheet1$` WHERE Portfolio_ID = '{ MERGEFIELD Portfolio_ID }'" It may need a bit more refinement to get it to work, but that's the starting point anyway. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv Rich wrote: I have a Mailmerge document and I want to use the FIELD option (Insert, Field on the toolbar) to display filtered results from a spreadsheet or access table. I am able to insert the DATABASE field but it shows all the rows the spreasheet( retail.xls). i was wondering if there is a way to filter the resutls so that ONLY the rows that have the same value as the PORTFILIO_ID wordfield are displayed. Any advice or info would be greatly appreciated |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| database field - merge single field from excel list | Microsoft Word Help | |||
| Need help with inserting database fields into Word document | Microsoft Word Help | |||
| If...Then...Else MAILMERGE field not inserting correctly | Mailmerge | |||
| Inserting database information | Microsoft Word Help | |||
| inserting auto-text from database | Microsoft Word Help | |||
Hybrid Mode
