• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

Nested IIF OR statement within IIF Statement

Hi,
My guess is there are multiple issues with my expression below, but here is what I am trying to accomplish in a query:
A) if "Active Med Vendor" has data in it (e.g., not blank) use that data to populate "Consolidated Med Vendor1"
B) If "Active Med Vendor" does not have data and if "Ret Under Med Vendor" is either blank or contains "Waived Coverage" use data from "Ret Over Med Vendor"
C) if "Active Med Vendor" does not have data and if "Reti Under Med Vendor" is not blank or does not not contain "Waived Coverage" than use data from "Ret Under Med Vendor"
D) If none of the above are true then populate with "Waived Coverage")

I can get various components of this to work, but am having trouble putting them all together.

Here is my current (obviously non-working) expression:
Consolidated Med Vendor1: IIf((Len([Active Med Vendor]))>0,[Active Med Vendor],IIF(IIF( [Ret Under Med Vendor] =""),OR IIF( [Ret Under Med Vendor] ="Waived Coverage"), [Ret Over Med Vendor] , [Ret Under Med Vendor] )

Thanks in advance
0
Hardly_an_Expert
Asked:
Hardly_an_Expert
  • 4
  • 2
1 Solution
 
JohnK813Commented:
Breaking this down:

A)
IIF(Len([Active Med Vendor])>0, [Active Med Vendor], ...)

B) and C)  I use Len<1 to account for 'blank' meaning either "" or Null.
IIF(Len([Ret Under Med Vendor])<1 OR [Ret Under Med Vendor] = "Waived Coverage", [Ret Over Med Vendor] , [Ret Under Med Vendor])

D) If I read correctly, you'll only get to option D if all Active and Over are blank, and Under is blank or Waived?  If so:
IIF(Len([Active Med Vendor])<1 AND (Len([Ret Under Med Vendor])<1 OR [Ret Under Med Vendor] = "Waived Coverage") AND Len([Ret Over Med Vendor])<1, "Waived Coverage", ...)

Putting it together:

IIF(Len([Active Med Vendor])<1 AND (Len([Ret Under Med Vendor])<1 OR [Ret Under Med Vendor] = "Waived Coverage") AND Len([Ret Over Med Vendor])<1, "Waived Coverage", IIF(Len([Active Med Vendor])>0, [Active Med Vendor], IIF(Len([Ret Under Med Vendor])<1 OR [Ret Under Med Vendor] = "Waived Coverage", [Ret Over Med Vendor] , [Ret Under Med Vendor])))

I know IIF statements aren't the most efficient, especially when they're nested like this.  But, this hopefully should answer your question.
0
 
Hardly_an_ExpertAuthor Commented:
I think I need to modify it a little - it doesn't appear to capture when "Active Med Vendor" is blank (e.g., len...<1) and "Ret Under Med Vendor" is blank as well.  

E.g. Rate now:
 first if is if all are blank put in "waived coverage"
second if is if Active is not blank put in active
third if if ret under is blank or waived put in ret over (i think this is missing if active blank qualifier... yes?)
Final put in ret under if all other not true

I think the third needs to be modified to say if active not blank AND if ret under not blank...
0
 
Hardly_an_ExpertAuthor Commented:
I thought this would be the fix, but it doesn't appear to work:

Consolidated Med Vendor1: IIf(Len([Active Med Vendor])<1 And (Len([Ret Under Med Vendor])<1 Or [Ret Under Med Vendor]="Waived Coverage") And Len([Ret Over Med Vendor])<2,"Waived Coverage",IIf(Len([Active Med Vendor])>0,[Active Med Vendor],IIf(Len([Active Med Vendor])<1 And (Len([Ret Under Med Vendor])<1 Or [Ret Under Med Vendor]="Waived Coverage",[Ret Over Med Vendor],[Ret Under Med Vendor])))
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Hardly_an_ExpertAuthor Commented:
I think this is the winner:

Consolidated Med Vendor1: IIf(Len([Active Med Vendor]) Is Null And (Len([Ret Under Med Vendor]) Is Null Or [Ret Under Med Vendor]="Waived Coverage") And Len([Ret Over Med Vendor]) Is Null,"Waived Coverage",IIf(Len([Active Med Vendor])>0,[Active Med Vendor],IIf(Len([Active Med Vendor]) Is Null And (Len([Ret Under Med Vendor]) Is Null Or [Ret Under Med Vendor]="Waived Coverage") And Len([Ret Over Med Vendor])>1,[Ret Over Med Vendor],[Ret Under Med Vendor])))
0
 
JohnK813Commented:
Glad you got it to work.  Sorry I didn't get a chance to come back for more help yesterday.
0
 
Hardly_an_ExpertAuthor Commented:
Not a problem, you set me on the right path.  It is more beneficial to figure it out than to just get the answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now