If "Yes" then "Yes or round as percentage

[John]

I am trying to import data from Excel into a Word document via mail merge.
All data in Excel is derived via a formula. One field <<Target>> is giving
me trouble because is may be a percentage e.g.98.5%, a Yes/No. or Null. I
have tried If Then and nothing seems to work to get the results I need.

If the value is Yes, then I need it to state Yes, If it is a percentage, I
need it to format as a percent and round to the 1st decimal. If it is null,
I need it to state "N/A"


Incidentally, I do not understand the merge switch concept and why it seems
to toggle between three states, then two. Resources for this would be
appreciated as well.

Thanks

 

[Graham Mayor]

What does the field produce without a switch for each condition?

The Yes, No and possibly the null should reproduce what you have in the
field. The only one that seems likely to cause trouble is the percentage
field. That being the case, on the face of it you need a nest of conditional
fields that test each possible result ie

{ IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }"} (Look for words
beginning upper case Y i.e. YES or Yes)
{ IF{ MERGEFIELD Target } = "N*" "{ MERGEFIELD Target }"}(Look for words
beginning upper case N i.e. NO or No)
{ IF{ MERGEFIELD Target } = "" "" } (Look for Null)
{ IF{ MERGEFIELD Target } = "Anything else" "{ ={ MERGEFIELD Target} * 100
\# "0.0%"}" }

so that would give you:

{ IF{ MERGEFIELD Target } = "Y*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD
Target } = "N*" "{ MERGEFIELD Target }" "{ IF{ MERGEFIELD Target } = "" ""
"{ ={ MERGEFIELD Target} * 100 \# "0.0%"}" }" }" }

Each pair of field brackets is inserted with CTRL+F9.

Switches are covered at http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[macropod]

Hi John,

You could probably do this with a compund field coded as:
{IF{MERGEFIELD Target}= "" "N/A" {IF{={MERGEFIELD Target}}= {MERGEFIELD Target} {={MERGEFIELD Target}*100 \# ",0%"} {MERGEFIELD
Target}}}

Alternatively, by using 5 copies of your «Target» mergefield, you could use:
{IF«Target»= "" "N/A" {IF{=«Target»}= «Target» {=«Target»*100 \# ",0%"} «Target»}}

In both cases, be careful to preserve the indicated spacing.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

 

[John]

Graham - Thanks for the response. Without a switch, the field returns a
non-rounded number when the source is populated with a number and 'N/A' or
null when the field is populated with 'N/A' or null respectively. I will
read through your website below and try your suggested switch then let you
know how it came out.
Thanks again.



qwerty

 

[John]

Revising my response from earlier today:
When I use the merge field without a switch it works fine so long as there
is a number as the source data. However, It returns "0" (zero) when the
source field states "Yes". This was an issue before I responded earlier but
I thought I had fixed it by closing and re-opening the two files.

While trying to implement your and macropod's solutions, I found that it
would still return "0" when the source was "Yes". I went back to the simple
merge field only to learn it is returning 0 even after closing and re-opening
the files.

The "No" and Null scenarios are not possible for my current field.
Simplifying the solution for "Yes" or a percentage format may give me the
starting point for the next field which does include "No" and nulls.

 

[macropod]

Hi John,

Check you mailmerge data connection type, and make sure it's DDE.

 

[Graham Mayor]

From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. (In Word
2007 it is Office Button > Word Options > Advanced > General > Confirm file
format conversion on open). Then when you attach the data source to the mail
merge main document, you will be given the option of using the DDE method of
connection which should read the data as you have it formatted in the table.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

There's an explanation of why this occurs at

http://tips.pjmsn.me.uk/t0003.htm

 

[John]

Revising my response from earlier today:
When I use the merge field without a switch it works fine so long as there
is a number as the source data. However, It returns "0" (zero) when the
source field states "Yes". This was an issue before I responded earlier but
I thought I had fixed it by closing and re-opening the two files.

While trying to implement your and macropod's solutions, I found that it
would still return "0" when the source was "Yes". I went back to the simple
merge field only to learn it is returning 0 even after closing and re-opening
the files.

The "No" and Null scenarios are not possible for my current field.
Simplifying the solution for "Yes" or a percentage format may give me the
starting point for the next field which does include "No" and nulls.

Thanks Graham - This worked.

 

[John]

Thanks Graham - This worked.

From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. (In Word
2007 it is Office Button > Word Options > Advanced > General > Confirm file
format conversion on open). Then when you attach the data source to the mail
merge main document, you will be given the option of using the DDE method of
connection which should read the data as you have it formatted in the table.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>