Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-21-2018, 03:20 PM
mtracy16 mtracy16 is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Atlanta, GA
Posts: 2
mtracy16 is on a distinguished road
Default Conditional checkbox mailmerge: coding "or" statements within IF statements

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!
Reply With Quote
  #2  
Old 05-21-2018, 05:01 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,468
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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 (http://www.msofficeforums.com/mail-m...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
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-22-2018, 08:20 AM
mtracy16 mtracy16 is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Atlanta, GA
Posts: 2
mtracy16 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
In the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum (http://www.msofficeforums.com/mail-m...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.
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!
Reply With Quote
  #4  
Old 05-22-2018, 02:03 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,468
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
Reply

Tags
check boxes, conditional merge fields, mailmerge field

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
legacy form fields conditional statements steelcity79 Word 3 05-05-2018 04:02 AM
saving conditional statements notalec Mail Merge 7 10-16-2017 09:34 PM
I know nothing about mail merge, but need help with conditional statements briyosa Word 1 03-27-2017 10:17 PM
Mailmerge certain text using multiple if statements Debs Mail Merge 4 06-19-2014 03:34 AM
"IF" "THEN" statements in Macros mfgeng_iss Misc 0 10-04-2007 07:31 AM


All times are GMT -7. The time now is 01:41 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft