[Rich]

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 OLEDBatabase
Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDBatabase 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 OLEDBon'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 OLEDBatabase
Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDBatabase 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 OLEDBon'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 OLEDBatabase Password=\"\";Jet
OLEDB:Engine Type=35;Jet OLEDBatabase 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 OLEDBon'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