Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-18-2018, 02:01 PM
Formd Formd is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Feb 2015
Location: TX
Posts: 28
Formd is on a distinguished road
Default If Statement with "and" and "Or

I hope you can help me with this problem. I am not a pro at this but each day I am learning something new.

Here is what I want to accomplish.

If {Field Rep} = "" AND if {Field Role} = Rec\Rpt\Ord OR Rec\Rpt OR Rpt\Ord OR Ord OR Rec then insert {Field Address} else {AltAdd}.



If {Rep} <> "" And {Rep} has data, my outcome should be:

Company #1
123 NW 19th St, Washington DC 11111

However, If {Rep} = "" then my outcome should be:

Joe Blo
8901 Coral Ave, New York, NY 22222

Below is what I tried to accomplish this but it didn't work.

{IF{= OR ({ COMPARE {Field Rep} = Rec\Rpt\Ord}, {COMPARE {Field Rep} = Rec\Rpt}, {COMPARE {Field Rep} = Rpt\Ord},{ COMPARE {Field Rep} = Ord}, { COMPARE {Field Rep} = Rec }) {Rep}
{Address}" "
{Altname}
{AltAdd}"}

I attached a spreadsheet to see an example of the db.

Thanks in advance for your help.
Attached Files
File Type: xls Reps Db.xls (33.5 KB, 6 views)
Reply With Quote
  #2  
Old 05-18-2018, 03:33 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

Are the braces (i.e. {}) in your attachment present in the actual workbook you're using for the mailmerge?

As for:
If {Rep} <> "" And {Rep} has data
that's the same as:
If {Rep} <> ""
since <> "" means there must be data whenever the test evaluates to TRUE...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-19-2018, 08:59 PM
Formd Formd is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Feb 2015
Location: TX
Posts: 28
Formd is on a distinguished road
Default

Thanks for responding.
Let me clarify a few things in my previous post.

If {Field Rep} = "" AND if {Field Role} = Rec\Rpt\Ord OR Rec\Rpt OR Rpt\Ord OR Ord OR Rec then insert {Field Address} else {AltAdd}.

The above is not the code I used, it only represents what I “want” to use to accomplish something.
“Rep” is the field that has a name OR it is left blank. So if that field is not left blank and if the next field called “Role” has any of the names underlined, then the result would merge from the “Address” field. So it’s a search of the field called Role. If any of the underlined is found, then whatever is in the Address merges ELSE Altname and AltAdd merges.


So, below is the actual code I tried using
{IF{= OR ({ COMPARE {Field Rep} = Rec\Rpt\Ord}, {COMPARE {Field Rep} = Rec\Rpt}, {COMPARE {Field Rep} = Rpt\Ord},{ COMPARE {Field Rep} = Ord}, { COMPARE {Field Rep} = Rec }) {Rep}
{Address}" "
{Altname}
{AltAdd}"}

Yes, I used the brackets which I thought is used if I am using OR to accomplish my goal. The excel only shows what my data choices could be but I am using Word 2016. I “hope” I did not confuse you.
Reply With Quote
  #4  
Old 05-19-2018, 09:59 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

What still isn't clear from your posts though, is whether the braces exist as the field names in your workbook.

As for your field representations, those too are incorrect, but I understand the intent.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-20-2018, 07:08 PM
Formd Formd is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Feb 2015
Location: TX
Posts: 28
Formd is on a distinguished road
Default

No, the braces are not a part of the field names. I thought I had to use the braces in an if statement if I want to include "or" in the if statement.
Reply With Quote
  #6  
Old 05-20-2018, 07:23 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

For what you've described, then, you could use a nested field coded as:
{IF{={IF{MERGEFIELD Role}= "Rec*" 1 0}+{IF{MERGEFIELD Role}= "Rpt*" 1 0}+{IF{MERGEFIELD Role}= "Ord*" 1 0} \# 0}> 0 {MERGEFIELD Address} {MERGEFIELD AltAdd}}
or:
{IF{={IF«Role»= "Rec*" 1 0}+{IF«Role»= "Rpt*" 1 0»+{IF«Role»= "Ord*" 1 0} \# 0}> 0 «Address» «AltAdd»}

Note: The field brace pairs (i.e. '{ }') for the above examples 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
  #7  
Old 05-21-2018, 09:55 AM
Formd Formd is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Feb 2015
Location: TX
Posts: 28
Formd is on a distinguished road
Default

This is Great. But it only should apply “if” {MERGEFIELD Rep} is blank. How can I include IF {MERGEFIELD Rep} = "" THEN do this:

{IF{={IF{MERGEFIELD Role}= "Rec*" 1 0}+{IF{MERGEFIELD Role}= "Rpt*" 1 0}+{IF{MERGEFIELD Role}= "Ord*" 1 0} \# 0}> 0 {MERGEFIELD Address} {MERGEFIELD AltAdd}}.
Reply With Quote
  #8  
Old 05-21-2018, 11:40 AM
Formd Formd is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Feb 2015
Location: TX
Posts: 28
Formd is on a distinguished road
Default

Well, I was able to get the following to work. I know it's not the best way.

{IF{MERGEFIELD Rep}= "" {IF{={IF«Role»= "Rec*" 1 0}+{IF«Role»= "Rpt*" 1 0»+{IF«Role»= "Ord*" 1 0} \# 0}> 0 «Address» }} {IF{MERGEFIELD Rep}<> "" {IF{={IF«Role»= "Rec*" 1 0}+{IF«Role»= "Rpt*" 1 0»+{IF«Role»= "Ord*" 1 0} \# 0}> 0 «AltAdd»}}

There is still a missing piece to the puzzle.
{IF{MERGEFIELD Rep}<> "" {IF{={IF«Role»= "Rec*" 1 0}+{IF«Role»= "Rpt*" 1 0»+{IF«Role»= "Ord*" 1 0} \# 0}> 0 «AltAdd»}}
This should include MERGEFIELD «Altname» + «AltAdd».Only «AltAdd» merges. When I include both fields only one merges.

Final product should have
Joe Blo (this would be the Altname field)
8901 Coral Ave, New York, NY 22222 (this would be the Altadd field)
Reply With Quote
  #9  
Old 05-21-2018, 02:37 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

It's not at all clear what you want. There are four possible conditions; viz.:
• Rep = "" & Role = Rec, Rpt or Ord
• Rep = "" & Role <> Rec, Rpt or Ord
• Rep <> "" & Role = Rec, Rpt or Ord
• Rep <> "" & Role <> Rec, Rpt or Ord

So far you seem to have assigned only:
• Rep = "" & Role = Rec, Rpt or Ord outputs «Address»
• Rep = "" & Role <> Rec, Rpt or Ord
• Rep <> "" & Role = Rec, Rpt or Ord outputs «AltAdd»
• Rep <> "" & Role <> Rec, Rpt or Ord

What is the required output for each condition?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 05-21-2018, 06:45 PM
Formd Formd is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Feb 2015
Location: TX
Posts: 28
Formd is on a distinguished road
Default

Here is the required output

• Rep = "" & Role = Rec, Rpt or Ord outputs «Altname» and «AltAdd»
• Rep = "" & Role <> Rec, Rpt or Ord outputs DONOTHING
• Rep <> "" & Role = Rec, Rpt or Ord outputs «Address»
• Rep <> "" & Role <> Rec, Rpt or Ord outputs DO NOTHING
Reply With Quote
  #11  
Old 05-21-2018, 09:35 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

In that case, what you need is:

{IF{={IF{MERGEFIELD Role}= "Rec*" 1 0}+{IF{MERGEFIELD Role}= "Rpt*" 1 0}+{IF{MERGEFIELD Role}= "Ord*" 1 0} \# 0}> 0 {IF {MERGEFIELD Rep}= "" "{MERGEFIELD AltName}¶
{MERGEFIELD AltAdd}" {MERGEFIELD Address}}}
or:
{IF{={IF«Role»= "Rec*" 1 0}+{IF«Role»= "Rpt*" 1 0}+{IF«Role»= "Ord*" 1 0} \# 0}> 0 {IF « Rep»= "" "« AltName»¶
« AltAdd»" « Address»}}
where the ¶ is a reall paragraph break or line break.

In effect, I've replaced:
{MERGEFIELD Address} {MERGEFIELD AltAdd}
in post 6 with:
{IF {MERGEFIELD Rep}= "" "{MERGEFIELD AltName}¶
{MERGEFIELD AltAdd}" {MERGEFIELD Address}}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #12  
Old 05-23-2018, 08:46 AM
Formd Formd is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Feb 2015
Location: TX
Posts: 28
Formd is on a distinguished road
Thumbs up

Works like a charm.
This is perfect and will help in completing so many other projects .
Thank you so much.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine "sort within groups" and "identify duplicate/unique values" in two different variables. civilcervant Excel 3 06-12-2017 07:27 AM
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N mikey386 Excel 2 12-11-2014 01:14 PM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas jocke321 Word VBA 2 12-10-2014 11:27 AM
IF statement just generates text "MERGEFIELD" in document - what am I doing wrong? Eri Mail Merge 1 03-03-2012 03:03 PM
How to choose a "List" for certain "Heading" from "Modify" tool? Jamal NUMAN Word 2 07-03-2011 03:11 AM


All times are GMT -7. The time now is 02:23 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