#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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.
|
#6
|
||||
|
||||
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 (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] |
#7
|
|||
|
|||
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}}. |
#8
|
|||
|
|||
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) |
#9
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#10
|
|||
|
|||
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 |
#11
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Works like a charm.
This is perfect and will help in completing so many other projects . Thank you so much. |
|
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 |