inserting the DATABASE field in a mailmerge with filtered reults

[Rich]

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

[Peter Jamieson]

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]

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]

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=\"\";User ID=Admin;Data
Source=C:\\myxlfiles\\retail.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDB:Database Password=\"\";Jet
OLEDB:Engine Type=35;Jet OLEDB:Database 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:Don'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.

[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=\"\";User 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:Database

Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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:Don'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=\"\";User 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:Database

Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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:Don'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]

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.

[Rich]

it finally worked when i used the [ ] and the ' around the MERGEFIELD

thank you sooooooooooo much!!!!

[Peter Jamieson]

Glad you got there :-)

[Rich]

Thanks,

One additional question, is there anyway I can limit the fields it returns
to only the following fields "User" and "User Name". I have tried playing
aroudn with it and cant get the desired result

This is the code i have that works

{DATABASE \d "C:\ Status Mailer.mdb" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User 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:Database

Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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:Don'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}

[Peter Jamieson]

Try using a Table Alias name, e.g.

"SELECT [W].[User], [W].[User Name] FROM [Wholesale_200908_submit] [W]

WHERE [Portfolio ID] = '{MERGEFIELD "Portfolio_ID"}'"

Some of the [] are probably redundant. Standard SQL syntax does not
require you to do this, but that's the only way it seems to work in Word.

Using an Alias name also seems to help prevent Word from confusing
column names in the DATABASE field from column names in the mail merge
data source (perhaps that's only a factor if you are using the same data
source for both those things).

[Rich]

Everything worked great.

The only issue I am having now is that I was using this for a E-mail - mail
merge and when I select the "Merge to Email" button it sends the "perfectly"
formatted email to the correct recipients but multipule times, i was
thiniking that this would only send 1 email per recipient with all the
details. Meaning 1 email to a recipient with all of the data from the [user]
and [user name] fields listed where they have the same [Portfolio ID], not 10
of the same emails to a recipient with the same data that is listed above.

Are there any workarounds?

[Peter Jamieson]

Does the data source that your mail merge is connected to definitely
only have 1 record for each email you want to send? My guess is that in
fact it has one record for each table row in the entire merge, which
would have the result you are seeing.

if that is the case, you would either need to redefine the data source
by - e.g. - defining a query in Access that returned only one record per
recipient, along with details that are associated with that recipient

(e.g. SELECT DISTINCT [Portfolio ID] FROM [Wholesale_200908_submit] )

or define a query in Word VBA which does much the same thing.

If that isn't the case, maybe you could describe in a bit more detail
what is in your data source, and how that relates to what is in the
table you are inserting.