• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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