If/And statement

[MarvInBoise]

Have Excel data merging into Word to create document/emails; if two fields
equal certain data, e.g., IF Carrier_Type=T AND Workers_Comp=Y, then I want
to insert certain paragraphs; OTHERWISE the merge does some other IF
statements, which I have working; I'm not sure how to do and IF/AND in Word,
or even if I can.

Thank you.

 

[Jean-Guy Marcil]

MarvInBoise was telling us:
MarvInBoise nous racontait que :

Have Excel data merging into Word to create document/emails; if two
fields equal certain data, e.g., IF Carrier_Type=T AND
Workers_Comp=Y, then I want to insert certain paragraphs; OTHERWISE
the merge does some other IF statements, which I have working; I'm
not sure how to do and IF/AND in Word, or even if I can.

Thank you.

You can use nested IF fields.
All you need to know is that an IF field works like this:

{IF some condition "then do this if true" "or do this if false"}

We use the "" to delimit the two possible results.


For example, let's say you have two bookmarked word in your document, each
bookmarked "Colour1" and "Colour2" respectively.

{IF Colour1 = "Black" "{ IF Colour2 = "White" "Both are True" "Only the
first one is true"}" "{IF Colour2 = "White" " Only the second one is true "
" None are True"}"}

Use CTRL-F9 to insert the {}.

If the text bookmarked by Colour1 is "Black" and the text bookmarked by
Colour2 is "White", "Both are true" will be displayed. If the second text is
not White, but the first is black, "Only the first one is true" will be
displayed (If the first was not black, we would not get to the second IF
field). And vice-versa.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[MarvInBoise]

THANKS, I'll give it a shot! I appreciate the quick response too!

 

[MarvInBoise]

Well, I'm a bit confused now that I've gotten around to looking at this!

Here is part of my code:

{IF MERGEFIELD Contract}="N""
Contract"}{IF MERGEFIELD Carrier_Type}="T""
Certificate of Carrier Authority"}

and it goes on; however, in the first statement IF Contract=N AND
Carrier_Type="B", then I don't want the text "Contract" at all.

I also have a second page that I don't want should certain conditions are
true (Y) and want nothing to generate if all conditions are true (Y).

Thanks for the help!

 

[Jean-Guy Marcil]

MarvInBoise was telling us:
MarvInBoise nous racontait que :

Well, I'm a bit confused now that I've gotten around to looking at
this!

Here is part of my code:

{IF MERGEFIELD Contract}="N""
Contract"}{IF MERGEFIELD Carrier_Type}="T""
Certificate of Carrier Authority"}

and it goes on; however, in the first statement IF Contract=N AND
Carrier_Type="B", then I don't want the text "Contract" at all.

I also have a second page that I don't want should certain conditions
are true (Y) and want nothing to generate if all conditions are true
(Y).

Thanks for the help!

First of all, MERGEFIELD are fields, ands so are IF fields.
Thus, each one needs its own pair of {}, this means that instead of:
{IF MERGEFIELD Contract}="N" "Contract" "Something else"}
you need
{IF {MERGEFIELD Contract}="N" "Contract" "Something else"}

Now, I do not have mergefield to test with, and I do not really want to
create a merge, instead, I used DOCPROPERTY Fields:

{IF {DOCPROPERTY CompanyName } = "ACME Inc." " {IF {DOCPROPERTY Client } =
"Road Runner" "Text You want because both are true" "Only the CompanyName
is true"}" "{IF { DOCPROPERTY Client } = "Road Runner" "Only the client is
true" "None are true"}"}

Replace {DOCPROPERTY CompanyName } by {MERGEFIELD Contract}, {DOCPROPERTY
Client } by {MERGEFIELD Carrier_Type}, "ACME Inc." by "N" and "Road Runner"
by " B" (or is it "T"' you used both in your post?); then insert the text
you want according to the condition result and you should be good to go.

I do not understand what you are trying to do with Y and your second page.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[MarvInBoise]

Thank you for your help, I have the paragraphs pulling correctly with your
help!

My next challenge: If I have all 8 fields equal "Y", then I don't want
anything to generate (like the entire letter); just pulling in various
paragraphs with the other testing.

Second challenge: If 4 of the fields are equal "Y", then I have a second
page to the letter I do not want generated.

So, can I test of all 8 fields are Y and enclose my entire letter in quotes?
Can I do the same thing for the second page?

Thanks so much; you have been a trememdous help!

 

[Jean-Guy Marcil]

MarvInBoise was telling us:
MarvInBoise nous racontait que :

Thank you for your help, I have the paragraphs pulling correctly with
your help!

My next challenge: If I have all 8 fields equal "Y", then I don't
want anything to generate (like the entire letter); just pulling in
various paragraphs with the other testing.

Second challenge: If 4 of the fields are equal "Y", then I have a
second page to the letter I do not want generated.

So, can I test of all 8 fields are Y and enclose my entire letter in
quotes? Can I do the same thing for the second page?

Thanks so much; you have been a trememdous help!

Dealing with 8 fields that needs to be nested with IF fields is quickly
going to lead to a nightmare if you are not organized.
Personally I would try VBA instead, but it can be done if you are methodical
and you are clear on what you want.

For example, if you want all 8 fields to be true, then you need something
like:

{IF {MERGEFIELD 1} = "Y" "{IF {MERGEFIELD 2} = "Y" "{IF {MERGEFIELD 3} = "Y"
"{IF {MERGEFIELD 4} = "Y" "{IF {MERGEFIELD 5} = "Y" "{IF {MERGEFIELD 6} =
"Y" "{IF {MERGEFIELD 7} = "Y" "{IF {MERGEFIELD 8} = "Y" "All are Y" "1-7 are
Y, but not 8"}" "1-6 are Y, but not 7"}" "1-5 are Y, but not 6"}" "1-4 are
Y, but not 5"}" "1-3 are Y, but not 4"}" "1-2 are Y, but not 3"}" "1 is Y,
but not 2"}" "All not Y"}

This works, but only sequentially, i.e. If 1,2,3 are Y, 4 is not, 5 to 8
are, you will get "1-3 are Y, but not 4", which does not tell you anything
about 5 to 8. For that, you need to test all other fields again in each "IF
NOT" part:

{IF {MERGEFIELD 1} = "Y" "{IF {MERGEFIELD 2} = "Y" "{IF {MERGEFIELD 3} = "Y"
"{IF {MERGEFIELD 4} = "Y" "{IF {MERGEFIELD 5} = "Y" "{IF {MERGEFIELD 6} =
"Y" "{IF {MERGEFIELD 7} = "Y" "{IF {MERGEFIELD 8} = "Y" "" "1-7 are Y, but
not 8"}" "1-6 are Y, but not 7"}" "1-5 are Y, but not 6"}" "1-4 are Y, but
not 5"}" "1-3 are Y, but not 4"}" "1-2 are Y, but not 3"}" "1 is Y, but not
2"}" "{IF {MERGEFIELD 2} <> "Y" "1 and 2 are not Y" "1 is not Y but 2 is
Y"}"}

Here I take into consideration the status of the second field in relation
with the first one, but not with the others.

Honestly, if you need all possible outcomes, this will quickly become a
nightmare...

If all you care about is if all are Y then use the first example and replace
all the text between quote with nothing. This means that as soon as one is
not Y or all of them are Y, nothing will appear.
Then, repeat with only 4 fields for the second page (Although I am still not
clear with this).

If you need to test for every possible combination between the 8 fields, I
think it would be safer with a macro, at least for me. I know that macropod
likes to do everything with fields and has a mind that can wrap itself
around all those different fields combination that you would need in that
case. If that it the case, start a new thread explaining in details what is
the relationships between the merge fields value and the text that should be
displayed and I am sure that as soon as he sees it, he will jump on board to
help.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org