Problems with Merge Codes

[Michael Koerner]

Using Word/Excel 2002 SP3

I have a Word File which merges Excel data just fine.

What I would like to do is modify the Word file to only accept the Excel data if
{MERGEFIELD active} is not blank.

The layout is a directory type Word table that has Name, Address, Telephone...
If the data source field "active" contains data, then enter the Name in Column
1, Address in Column 2 etc if the data source field "active" is blank, then skip
and go
to the next non blank record.

Any assistance would be greatly appreciated.

 

[Peter Jamieson]

Typically you should use the Mail Merge filtering options to select records
in the data source (in Word 2002/2003 open the Edit Recipients dialog box,
select one of the column headings, and choose "Advanced..." to get to the
relevant dialog box. In Word 2000 and earlier, use the Query Options button
in Mail Merge Helper).

However,
a. there are problems in some versions of Word where Word may lose the data
source if you specify filters.
b. maybe you meant something slightly different?

Peter Jamieson

 

[Michael Koerner]

Clicking on the "Mail Merge Recipients" button does not produce an "Advanced
Option" in my 2002 version of Word. Maybe I'm looking in the wrong place.

I thought it could be done using (which I know nothing about) an IF statement.
Something to the effect that:

{IF} {MERGEFIELD active} is = to "y" then enter the
{MERGEFIELD name} and the rest of the {MERGEFIELD's} for that particular record.

--

Regards
Michael Koerner


Typically you should use the Mail Merge filtering options to select records
in the data source (in Word 2002/2003 open the Edit Recipients dialog box,
select one of the column headings, and choose "Advanced..." to get to the
relevant dialog box. In Word 2000 and earlier, use the Query Options button
in Mail Merge Helper).

However,
a. there are problems in some versions of Word where Word may lose the data
source if you specify filters.
b. maybe you meant something slightly different?

Peter Jamieson

 

[Peter Jamieson]

When you open ail erge Recipients, you should see the data fro the data
source displayed with column headings. In each column heading there should
be a sall triangular drop-down arrow. If you click that, you should see
options to filter on various values in that particular column. So if you
click on the header of the Active column, you may be able to select the
filter you need. If not, at the bottom of the list you should see an
(Advanced...) option which leads to the dialog I mentioned.

I thought it could be done using (which I know nothing about) an IF
statement.
Something to the effect that:

{IF} {MERGEFIELD active} is = to "y" then enter the
{MERGEFIELD name} and the rest of the {MERGEFIELD's} for that particular
record.

Yes, you could enclose your entire text inside the result of an IF field,
e.g.

{ IF "{ MERGEFIELD active } = "y" "the complete text of your document
including fields" "" }

but if you do that, you will get a blank page (i.e. not quite nothing) for
each record you are trying to exclude, if you are using a Letter erge. For a
Catalog/Directory Merge you should be OK. In either case, you may find that
complex foratting within the IF result does not behave properly.

You can also put the following at the start of your ail merge main document

{ SKIPIF "{ MERGEFIELD active } <> "y" }

but I would check that that does what you want, and be aware that Microsoft
advises against the use of SKIPIF (see Word Help) and that it may interact
badly with { NEXT } fields.

Peter Jamieson

 

[Michael Koerner]

Peter;

Much appreciated and thank you very much. The { SKIPIF "{ MERGEFIELD active } <>
"y" } worked
like a charm, as did the "Advanced Option" which I found to be much quicker.
Does that option stay once you leave the document? Or, is it something you have
to select each time you run the merge?


--

Regards
Michael Koerner


When you open ail erge Recipients, you should see the data fro the data
source displayed with column headings. In each column heading there should
be a sall triangular drop-down arrow. If you click that, you should see
options to filter on various values in that particular column. So if you
click on the header of the Active column, you may be able to select the
filter you need. If not, at the bottom of the list you should see an
(Advanced...) option which leads to the dialog I mentioned.

I thought it could be done using (which I know nothing about) an IF
statement.
Something to the effect that:

{IF} {MERGEFIELD active} is = to "y" then enter the
{MERGEFIELD name} and the rest of the {MERGEFIELD's} for that particular
record.

Yes, you could enclose your entire text inside the result of an IF field,
e.g.

{ IF "{ MERGEFIELD active } = "y" "the complete text of your document
including fields" "" }

but if you do that, you will get a blank page (i.e. not quite nothing) for
each record you are trying to exclude, if you are using a Letter erge. For a
Catalog/Directory Merge you should be OK. In either case, you may find that
complex foratting within the IF result does not behave properly.

You can also put the following at the start of your ail merge main document

{ SKIPIF "{ MERGEFIELD active } <> "y" }

but I would check that that does what you want, and be aware that Microsoft
advises against the use of SKIPIF (see Word Help) and that it may interact
badly with { NEXT } fields.

Peter Jamieson

 

[Peter Jamieson]

The option /should/ stay, but in Word 2002 at least there were some errors
to do with sorts and filters whereby Word lost its connection altogether
when you saved, so
a. try it and see
b. if absolutely necessary and you are willing to do a bit of VBA, the
connection can probably be made with a suitable bit of VBA in an AutoOpen
macro.

Peter Jamieson

 

[Michael Koerner]

What's VBA <g>

--

Regards
Michael Koerner


The option /should/ stay, but in Word 2002 at least there were some errors
to do with sorts and filters whereby Word lost its connection altogether
when you saved, so
a. try it and see
b. if absolutely necessary and you are willing to do a bit of VBA, the
connection can probably be made with a suitable bit of VBA in an AutoOpen
macro.

Peter Jamieson

 

[Peter Jamieson]

I try to live without it too

Over & out for now,

Peter Jamieson