Mail Merge from Excel - Skip If?

[KevinM]

I have been successfully using mail merge in a basic way for some time,
however I thought I would try and be a bit smarter and now I am stuck!

I am merging salary letters, however certain benefits to not apply to
all employees. Eg Car Allowance. So in Excel I have a spreadheet
showing
Salary Car Allowance
B Smith 10,000 3,000
S Jones 15,000 0

In Word I have a letter with the following merge fields eg

I am pleased to confirm your salary as <<salary>>
and your car allowance will remain as <<car allowance>>

What I would like to do is only add the second line relating to car
allowance if the allowance shown in Excel is greater than zero.

Can anyone help - I have tried the help pages and looked at the FAQs
but I cannot seem to find a simple answer - Thanks?

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Kevin,

Using Ctrl+F9 to insert each pair of field delimiters { }

I am pleased to confirm your salary as { MERGEFIELD "Salary" }{IF {
MERGEFIELD "CarAllowance" } > 0 " and your car allowance will remain as {
MERGEFIELD "Car Allowance" }." "."

and Alt+F9 to toggle them off.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Cindy M -WordMVP-]

Hi KevinM,

What you'll need is an IF field that suppresses the complete line if
the value for the field is zero. It would look something like this:

{ IF { Mergefield Car_Allowance } <> 0 "The text here, press ENTER¶
" "" }

Note that the paragraph mark ¶ is also part of the conditional in the
"quotes".

Use Ctrl+F9 to insert empty pair of field brackets for the IF. The
Mergefields you can insert in the usualy way. You'll need to use Alt+F9
to toggle the field codes on/off. (Inserting a merge field will
probably toggle them off, and you'll need to turn them back on to
finish.)

I am merging salary letters, however certain benefits to not apply to
all employees. Eg Car Allowance. So in Excel I have a spreadheet
showing
Salary Car Allowance
B Smith 10,000 3,000
S Jones 15,000 0

In Word I have a letter with the following merge fields eg

I am pleased to confirm your salary as <<salary>>
and your car allowance will remain as <<car allowance>>

What I would like to do is only add the second line relating to car
allowance if the allowance shown in Excel is greater than zero.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[hkbs]

I would be really interested to hear if the solutions given worked for
KevinM; I could not get any to work! I typed the instructions exactly as
stated and just replaced the brackets with Ctrl + F9

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi hkbs,

There was a missing closing } in my field construction

I am pleased to confirm your salary as { MERGEFIELD "Salary" }{IF {
MERGEFIELD "CarAllowance" } > 0 " and your car allowance will remain as {
MERGEFIELD "Car Allowance" }." "."

should have been

I am pleased to confirm your salary as { MERGEFIELD "Salary" }{IF {
MERGEFIELD "CarAllowance" } > 0 " and your car allowance will remain as {
MERGEFIELD "Car Allowance" }." "}."

It does rely on there being a 0 in the CarAllowance field of the data source
which I assume would be the case with the data source being Excel.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP