Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2009
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with simple nested IF statements for merge field

    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

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Star Lounger
    Join Date
    May 2009
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    Star Lounger
    Join Date
    May 2009
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, Andrew! Your code is a big help and will get me on the road to completing this form. Charlie charlie6067

  6. #6
    Star Lounger
    Join Date
    May 2009
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Follow up question to Andrew regarded nested IF statements

    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.

    Thanks!
    Charlie
    charlie6067
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    May 2009
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code now working - Thanks Andrew!

    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
    Attached Files Attached Files

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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 {={IFCH1= "" 0 1}+{IFCH2= "" 0 1}+{IFCH3= "" 0 1}+{IFCH4= "" 0 1}+{IFCH5= "" 0 1}+{IFCH6= "" 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}
    Last edited by macropod; 2011-04-05 at 21:07.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Star Lounger
    Join Date
    May 2009
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •