Word Mail Merge question??

[Jenny]

Hi,

I am new to mailmerge. I have an access query as data
source with following fields and data values;


MemberID Test1 Test2 Test3 Test4
01110221 NA EyeExam LDL NA
01110222 Hb1c NA LDL Micro
01110223 Hb1c NA NA Micro
01110224 NA NA NA NA

In my main document(letter), I wanted to inform my
members that if they missed any of the four tests. NA
indicates the missing test. In some case, member just
missed one test, in some case, member missed all the four
tests. I wanted to tell each member in different letter
the following information:


member 01110221 that he/she missed Hb1c, and Micro.
member 01110222 that he/she missed Eye Exam.
member 01110223 that he/she missed Eye Exam, and LDL-C.
member 01110224 that he/she missed Hb1c,Eye Exam,LDL-C,
and Micro.

I know how to show those missing(NA) test correctly, but
how can I use the "Comma" or "Period" and the word "and"
correctly in different cases? Anyone can give me any
ideas? Thanks for your help.
Jenny

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Jenny,

If you did not need to have the commas and/or the "and", it just a case of
four simple If...Then...Else statements. The requirement for the commas and
or the "and" requires the use of what grows to become a very complex series
of nested If...Then...Else statements as there is a total of eleven
possibilties to be dealt with. If you assign a value of 1 to NA for Test1,
3 to NA for Test2, 5 for NA for Test3 and 7 for NA for Test4 and then total
the values for the four test for each Member, you will get a number that is
unique for each possible combination of tests that are missed. What I would
do is create a function in Access that used the Select Case Statement to
assign the required string to each of those unique values and then use that
function in a Query in Access to return the appropriate string for each
Member.

I started to create the necessary If..Then..Else field that you would need
in the mailmerge main document and got as far as the following before I came
to the conclusion that there was better way:

{·IF { MERGEFIELD Test1 }·= "NA" "Hb1c" "" }{ IF { MERGEFIELD Test 2 } =
"NA" {·IF { MERGEFIELD·Test1·}·=·"NA"·"Hb1c ,·EyeExam "·"" } " EyeExam " }
{·IF·{ ·MERGEFIELD·Test 3 }·=·"NA"·{·IF·{ MERGEFIELD·Test 2 }·=·"NA"·{
IF·{·MERGEFIELD·Test1 "Hb1c ,·EyeExam ,·LDL "·"" } " EyeExam ,·LDL " }
{·IF·{ MERGEFIELD·Test1 }·=·"NA"·"Hb1c LDL "·"" } " LDL " }{ IF·{
·MERGEFIELD·Test 4·} =·"NA"· {· IF· { ·MERGEFIELD·Test 3
IF·{·MERGEFIELD·Test 2 } =·"NA"·{ IF·{ MERGEFIELD·Test1 } =·"NA"·"Hb1c
,·EyeExam LDL and Micro "·"" } " EyeExam ,·LDL ·and·Micro "·"" }·" LDL
and·Micro " }·"Micro " }

The above is far from complete.

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

 

[Peter Jamieson]

On the whole I agree. but things can probably be made to look a bit simpler
via e.g.

{ SET mt1 ""
}{ SET mt2 ""
}{ SET mt3 ""
}{ SET mt4 ""
}{ SET mtc 0
}{ IF { MERGEFIELD Test1 } = "NA" "{ SET mtc 1 }{ SET mt1 "Hb1c" }" ""
}{ IF { MERGEFIELD Test2 } = "NA" "{ SET mtc { ={ REF mtc }+1 } }{ SET
"mt{ REF mtc }" "EyeExam" }" ""
}{ IF { MERGEFIELD Test3 } = "NA" "{ SET mtc { ={ REF mtc }+1 } }{ SET
"mt{ REF mtc }" "LDL" }" ""
}{ IF { MERGEFIELD Test4 } = "NA" "{ SET mtc { ={ REF mtc }+1 } }{ SET
"mt{ REF mtc }" "Micro" }" ""
}{ IF { REF mtc } = 1 "You missed { REF mt1 }" ""
}{ IF { REF mtc } = 2 "You missed { REF mt1 } and { REF mt2 }" ""
}{ IF { REF mtc } = 3 "You missed { REF mt1 }, { REF mt2 } and { REF mt3 }"
""
}{ IF { REF mtc } = 4 "You missed { REF mt1 }, { REF mt2 }, { REF mt3 } and
{ REF mt4 }" ""
}

 

[Jenny]

Hi,

Great thanks to both of your help.
I am really new to word mail merge, so I am not even sure
where I should to put the following code to try. Could
you let me know how I use the following code. I
desperately need the help.
Thanks,Jenny

 

[Peter Jamieson]

You insert it directly into the document. But the curly braces {} are not
the regular ones on the keyboard, they are special "field code braces" that
you can insert using ctrl-F9. Everything else can be entered using the
keyboard. You can use alt-F9 to toggle between "field code" view and "field
results" view. You can select one or more field codes and press F9 to update
their results.

Strictly speaking you do not need to lay the field codes out in the way I
have - where I have put

{ SET mt1""
}{ SET mt2 ""
}

and so on, you can just use

{ SET mt1 "" }{ SET mt2 "" }

However, you should not use

{ SET mt1 "" }
{ SET mt2 "" }

because then you will get unwanted paragraph marks in the output.

--
Peter Jamieson - Word MVP

Hi,

Great thanks to both of your help.
I am really new to word mail merge, so I am not even sure
where I should to put the following code to try. Could
you let me know how I use the following code. I
desperately need the help.
Thanks,Jenny