I am in the process of updating a form containing several checkbox mailmerge fields to reflect changes in testing practices for microbial specimens. The mailmerge document is saved as a word 1997-2003 document. The data source is an excel 1997-2003 workbook.

Fields relevant to my problem are:
LABTEST1
DISEASE

Possible outcomes for these two character variables are:
LABTEST1 = "Culture" or "Pcr"
DISEASE = "Campylobacter" "STEC" "Salmonella" "Shigella" "Yersinia" "Listeria" or "Vibrio"

Specimens that test positive by "Culture" can be categorized as an Enteric Isolate or as Special Bacteriology depending on the DISEASE type. Specimens that test positive by "Pcr" are all categorized as Clinical Specimens regardless of DISEASE type.

I was able to write the code for the "Pcr" specimens easily as they all are categorized as Clinical Specimens. The code below successfully creates a check box next to the category Clinical Specimen for "Pcr" positive specimens.
{IF {MERGEFIELD LABTEST1}= "Pcr" þo}



I also was able to write code for the subfield checkboxes for both "Pcr" specimens & "Culture" specimens that specify the "DISEASE" (if labtest1 AND disease then check then check the box). For example, specimens that tested positive for "Campylobacter" by "Culture" successfully insert a checkbox with the following conditional coding:
{IF {MERGEFIELD LABTEST1}="Culture" {IF {MERGEFIELD DISEASE}="Campylobacter" þo} o }

Similarly, this same coding also works to specify "DISEASE" for "Pcr" specimens. The only difference is that the "LABTEST1" field is switched from "Culture" to "Pcr". Thus, specimens that tested positive for "Campylobacter" by "Pcr" successfully insert a checkbox with the following code:
{IF {MERGEFIELD LABTEST1}="Pcr" {IF {MERGEFIELD DISEASE}="Campylobacter" þo} o }

Here is where the problem starts I am having trouble writing code to distinguish between two different categories for culture specimens. Specimens that test positive for one of the above "DISEASE" options via "Culture" either require further testing as an Enteric Isolate (this applies for "Campylobacter" "STEC" "Salmonella" "Shigella" "Yersinia") or they require further testing as Special Bacteriology (this applies for "Listeria" "Vibrio"). I have attached a screenshot of the form so you can view how these are grouped Screenshot of mailmerge form.JPG.

Basically, to check the Enteric Isolate box I need to write a code that says IF LABTEST1= "Culture" AND IF DISEASE= "Campylobacter" OR "STEC" OR "Salmonella" OR "Shigella" OR "Yersinia" THEN check the Enteric Isolate box. Similarly, to check the Special Bacteriology box I need to write a code that says IF LABTEST1= "Culture" AND IF DISEASE= Listeria" OR "Vibrio" THEN check the Special Bacteriology box.

Since the Special Bacteriology checkbox is used for fewer DISEASE types, I started trying out different iterations of conditional codes with or statements for the Special Bacteriology field. I haven't had any success so for, but the closest I have gotten is with the following code:

{IF {MERGEFIELD LABTEST1}="Culture" {{IF {MERGEFIELD DISEASE}="Yersinia"}OR{IF {MERGEFIELD DISEASE}="Vibrio"} þo} o}

Please send me your help & recommendations! I know there must be a way to do this, I just haven't cracked it yet. Thanks for your help!

Try field coding based on:
{IF{={IF{MERGEFIELD LABTEST1}= "Culture" 1 0}*({IF{MERGEFIELD DISEASE}= "Campylobacter" 1 0}+{IF{MERGEFIELD DISEASE}= "STEC" 1 0}+{IF{MERGEFIELD DISEASE}= "Salmonella" 1 0}+{IF{MERGEFIELD DISEASE}= "Shigella" 1 0}+{IF{MERGEFIELD DISEASE}= "Yersinia" 1 0})}> 0 "Checked" "Unchecked"}
or:
{IF{={IF«LABTEST1»= "Culture" 1 0}*({IF«DISEASE»= "Campylobacter" 1 0}+{IF«DISEASE»= "STEC" 1 0}+{IF«DISEASE»= "Salmonella" 1 0}+{IF«DISEASE»= "Shigella" 1 0}+{IF«DISEASE»= "Yersinia" 1 0})}> 0 "Checked" "Unchecked"}

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. Likewise, the chevrons (i.e. '« »') are part of the actual mergefields - which you can insert from the 'Insert Merge Field' dropdown (i.e. you can't type or copy & paste them from this message, either). The spaces represented in the field constructions are all required.

In the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum (https://www.msofficeforums.com/mail-...ps-tricks.html) you'll find a macro under the heading Convert Text Representations of Fields to Working Fields that will convert either of the above into a working field code.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I understand everything in your post until the macro... how do I insert the macro into the excel spreadsheet to change the coding of DISEASE and LABTEST1 from character to numeric 0,1 coding using the macro? Do I need to edit your macro to specify to convert only these variables? I am not understanding how to connect the excel spreadsheet to the macro & how to direct it to those specific variables. I am not familiar with macros at all so any further explanation/tips would be helpful.

Thank you!

The macro is a Word macro - for converting the field code string in post 2 into a working field code in your mailmerge main document. There are links to instructions for installing & using macros at the top of the page in the link I gave you.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]