[Jadams1]

Hi!
I am looking to set up a word template so that when a new data source is
used in the merge it will automatically filter the data source with the
criteria used in a past merge. Is this possible in word? If so, how would I
go about doing this?
Thanks!

[Peter Jamieson]

It would certainly not be straightforward in the general case, because...
a. there are (at least!) four sets of criteria for including records
in a merge (ignoring the fact that you can also use { SKIPIF } fields to
ignore records in some types of merge), i.e.
- you choose a data source - a file, a table in a database, a
workbook/worksheet
- you filter using the dropdowns in Mail Merge Recipients (and/or
the form in "Query Options")
- you include/exclude individual records in Mail Merge Recipients
- you specify the start and finish data source record numbers when
you initiate the merge
b. When the user specifies criteria using the dropdowns or Query
options, from a programming perspective you don't get to see what the
user specified in those dropdowns. At best, what you get to see is the
SQL code that Word generated as a consequence of the user's choices. The
SQL generated depends partly on the type of data source - e.g., when the
data source is a Word document, Word uses a very smple built-in dialect
of SQL. If the data source is a Jet database, Word may use a Jet SQL
dialect.

If the user chose a different data source of the same type (e.g.
switched from one Access data source to another) maybe you could just
re-use the SQL WHERE clause or some such. But if the user chose a
different type of data source altogether, you would in effect have to
translate from one SQL dialect to another.
c. When the user selects individual records, you may find it difficult
to determine programmatically what they selected because the interface
is unreliable and may hang/crash Word (I forget the details, because the
interface is so unreliable there's not much point in remembering them,
but I 'll look again if you really insist)

i.e., broadly speaking, the more narrow the choices available to your
users, the simpler it would be to impose the same selection criteria
given a change of data source. If your users could choose any old data
source, things could be pretty hard, and perhaps in that case it might
be better to present your own selection criteria forms and generate your
own queries, etc. etc.


Peter Jamieson

http://tips.pjmsn.me.uk

Jadams1 wrote:
Hi!
I am looking to set up a word template so that when a new data source is
used in the merge it will automatically filter the data source with the
criteria used in a past merge. Is this possible in word? If so, how would I
go about doing this?
Thanks!

[Jadams1]

Thank you for your response...
The file that will always be used for the mail merge I am trying to set up
is an excel document. The mail merge is going to be an acknowledgement letter
based on identification criteria of a person--ie. a donation is made for a
certain amount and in order to receive a letter they have to donate equal or
grater than that amount, which would set up the filter in word. I want to be
able to open the template, add the data source, then preview and print
without having to apply the filter again. This is a process that runs
everyday and I have 10 different letters that need to be sent out. Just
trying to see if there is a way to streamline this process.

"Peter Jamieson" wrote:

It would certainly not be straightforward in the general case, because...
a. there are (at least!) four sets of criteria for including records
in a merge (ignoring the fact that you can also use { SKIPIF } fields to
ignore records in some types of merge), i.e.
- you choose a data source - a file, a table in a database, a
workbook/worksheet
- you filter using the dropdowns in Mail Merge Recipients (and/or
the form in "Query Options")
- you include/exclude individual records in Mail Merge Recipients
- you specify the start and finish data source record numbers when
you initiate the merge
b. When the user specifies criteria using the dropdowns or Query
options, from a programming perspective you don't get to see what the
user specified in those dropdowns. At best, what you get to see is the
SQL code that Word generated as a consequence of the user's choices. The
SQL generated depends partly on the type of data source - e.g., when the
data source is a Word document, Word uses a very smple built-in dialect
of SQL. If the data source is a Jet database, Word may use a Jet SQL
dialect.

If the user chose a different data source of the same type (e.g.
switched from one Access data source to another) maybe you could just
re-use the SQL WHERE clause or some such. But if the user chose a
different type of data source altogether, you would in effect have to
translate from one SQL dialect to another.
c. When the user selects individual records, you may find it difficult
to determine programmatically what they selected because the interface
is unreliable and may hang/crash Word (I forget the details, because the
interface is so unreliable there's not much point in remembering them,
but I 'll look again if you really insist)

i.e., broadly speaking, the more narrow the choices available to your
users, the simpler it would be to impose the same selection criteria
given a change of data source. If your users could choose any old data
source, things could be pretty hard, and perhaps in that case it might
be better to present your own selection criteria forms and generate your
own queries, etc. etc.


Peter Jamieson

http://tips.pjmsn.me.uk

Jadams1 wrote:
Hi!
I am looking to set up a word template so that when a new data source is
used in the merge it will automatically filter the data source with the
criteria used in a past merge. Is this possible in word? If so, how would I
go about doing this?
Thanks!

[Peter Jamieson]

Just
trying to see if there is a way to streamline this process.

Assuming you are starting from the same Excel workbook in each case and
using the same selection criteria, I would probably turn the problem
around and
a. do your selection in Excel
b. (probably) copy/paste the filtered results to another worksheet or,
for maximum flexibility, a sheet in another book (or even into a Word
document)
c. use that as your data source

i.e. the data source is then always the same, and always unfiltered.

Depending on your Word version, connection method etc. you may need to
ensure that all the Word documents that used this data source are closed
before doing the sort/filter operation.


Peter Jamieson

http://tips.pjmsn.me.uk

Jadams1 wrote:
Thank you for your response...
The file that will always be used for the mail merge I am trying to set up
is an excel document. The mail merge is going to be an acknowledgement letter
based on identification criteria of a person--ie. a donation is made for a
certain amount and in order to receive a letter they have to donate equal or
grater than that amount, which would set up the filter in word. I want to be
able to open the template, add the data source, then preview and print
without having to apply the filter again. This is a process that runs
everyday and I have 10 different letters that need to be sent out. Just
trying to see if there is a way to streamline this process.

"Peter Jamieson" wrote:

It would certainly not be straightforward in the general case, because...
a. there are (at least!) four sets of criteria for including records
in a merge (ignoring the fact that you can also use { SKIPIF } fields to
ignore records in some types of merge), i.e.
- you choose a data source - a file, a table in a database, a
workbook/worksheet
- you filter using the dropdowns in Mail Merge Recipients (and/or
the form in "Query Options")
- you include/exclude individual records in Mail Merge Recipients
- you specify the start and finish data source record numbers when
you initiate the merge
b. When the user specifies criteria using the dropdowns or Query
options, from a programming perspective you don't get to see what the
user specified in those dropdowns. At best, what you get to see is the
SQL code that Word generated as a consequence of the user's choices. The
SQL generated depends partly on the type of data source - e.g., when the
data source is a Word document, Word uses a very smple built-in dialect
of SQL. If the data source is a Jet database, Word may use a Jet SQL
dialect.

If the user chose a different data source of the same type (e.g.
switched from one Access data source to another) maybe you could just
re-use the SQL WHERE clause or some such. But if the user chose a
different type of data source altogether, you would in effect have to
translate from one SQL dialect to another.
c. When the user selects individual records, you may find it difficult
to determine programmatically what they selected because the interface
is unreliable and may hang/crash Word (I forget the details, because the
interface is so unreliable there's not much point in remembering them,
but I 'll look again if you really insist)

i.e., broadly speaking, the more narrow the choices available to your
users, the simpler it would be to impose the same selection criteria
given a change of data source. If your users could choose any old data
source, things could be pretty hard, and perhaps in that case it might
be better to present your own selection criteria forms and generate your
own queries, etc. etc.


Peter Jamieson

http://tips.pjmsn.me.uk

Jadams1 wrote:
Hi!
I am looking to set up a word template so that when a new data source is
used in the merge it will automatically filter the data source with the
criteria used in a past merge. Is this possible in word? If so, how would I
go about doing this?
Thanks!