Need help with simple nested IF statements for merge field

Topic

New Reply

My company’s using Word 2002. VBA is not possible to use for this merge so Word fields are my only choices.

The four database fields are: CH1 CH2 CH3 CH4 CH5 CH6 and each may or may not hold a child’s name in the data stream.

How is a nested IF statement written to verify that only one of the fields contain data? This is used in determining if “child” or “children” display in the merged document. If only one name out of six has data, “child” displays on form. If more than one name (or all names) have data, “ren” displays on form immediately following “child” for “children”.

To restate, if only one name out of six has data, do nothing because “child” will already be in form. If more than one name has data, add “ren” to the end of “child”.

I’ve tried several combinations of nested IF statements but with no success. Have also searched forum for answers but didn’t see anything specific to this question.

Thanks for the help!
Charlie
charlie6067

Reply | Quote

Replies

Does the list always fill from CH1? I mean is it possible to have a CH2 without having a CH1?

Assuming that you have to have one child before you can have two then you should only have to test the value of CH2.

Reply | Quote

Thanks Andrew for quick reply. Your assumption is correct. The one field that contains data could be any of the six listed. Could you please get me started with a couple If statements and I can do the rest. Thanks

Reply | Quote

It would probably be easier to get the database to produce the answer in another field via your query but in Word the following nesting works

{IF { = {MERGEFIELD CH1} = “” 0 1} + {MERGEFIELD CH2} = “” 0 1} + {MERGEFIELD CH3} = “” 0 1} + {MERGEFIELD CH4} = “” 0 1} + {MERGEFIELD CH5} = “” 0 1} + {MERGEFIELD CH6} = “” 0 1}} > 1 children child}

Basically what I did was assign a value of 0 for each blank field and 1 for each non-blank field, then added these up to return a count which then fed the outer IF statement.

Reply | Quote

Andrew – I was able to recreate your example (with minor variations) using field braces but a calculation error 1 displays. The Word and Excel files are attached for merging. I would greatly appreciate it if you could please see what I’ve coded correctly incorrectly. For example, I was unable to enclose just the merge field {MERGEFIELD CH1} in braces without having an extra left brace over at end of each expression. This is the most complex IF statement I’ve ever worked on and it will open new coding possibilities once I get it right. [/FONT][/COLOR]
[/FONT][/COLOR]
Thanks![/FONT][/COLOR]
Charlie [/FONT][/COLOR]
charlie6067[/FONT][/COLOR]

Reply | Quote

Andrew – I worked all morning with the code and got it to work. It’s attached in Word if it’s of value to others. It won’t work with the previously attached Excel file as there are ten kids in all. Part of my problem was not having a lot of experience with this level of IF fields. Now, I’m ready to take on any IF field Thanks for your help!

Charlie
charlie6067

Reply | Quote

Thank you, Andrew! Your code is a big help and will get me on the road to completing this form. Charlie charlie6067

Reply | Quote

I would encode two fields:
1. {SET CHN {={IF{MERGEFIELD CH1}= “” 0 1}+{IF{MERGEFIELD CH2}= “” 0 1}+{IF{MERGEFIELD CH3}= “” 0 1}+{IF{MERGEFIELD CH4}= “” 0 1}+{IF{MERGEFIELD CH5}= “” 0 1}+{IF{MERGEFIELD CH6}= “” 0 1}}}
or:
{SET CHN {={IF«CH1»= “” 0 1}+{IF«CH2»= “” 0 1}+{IF«CH3»= “” 0 1}+{IF«CH4»= “” 0 1}+{IF«CH5»= “” 0 1}+{IF«CH6»= “” 0 1}}}
and:
2. {=CHN-1 # “‘Children’;;’Child'”}
The first field creates a bookmark named ‘CHN’ that holds the count of children. The second field subtracts 1 from this and uses a numeric picture switch to toggle between child/children. This is better than having to use the same compund field test everywhere you might need the child/children variable in the document. And, if you need the actual child/children count somewhere in the document, it’s as easy as inserting a field coded as {=CHN # 0}

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thank you Paul for the reply and suggestions. You and Andrew have expanded my knowledge of merge fields and I want to continue learning. It’s a bit confusing to me when I start adding all the braces in Word while looking the code examples. But, if it was easy, everyone would do it

Charlie
charlie6067

Reply | Quote

Hi Charlie,

If you need to represent a field code in text form, or you see a text representation of a field code, and you want to do a conversion from one to the other, there’s a couple of macros for this at:
http://www.gmayor.com/export_field.htm
(The macro for converting text representations of field codes to real field codes works with the first form of the code in my previous post)

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote