Nested IIF OR statement within IIF Statement
Posted on 2005-04-05
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