Here is my IIf statement right now (which works, just not as sophisticated as I need it to be):

Eligibility: IIf(([DATEFROM]<[dbo_RVS_MEMB_HPHISTS].[OPFROMDT]) Or ([DATEFROM]>[dbo_RVS_MEMB_HPHISTS].[OPTHRUDT]),"Not Eligible","Eligible")

I am determining Member\Patient Eligibility and the thing here is that the members have Eligibility Sequences\Histories.

For example, let's say, I joined a Health Plan called IEHP on 1/1/09 and ended with IEHP on 12/31/09 and then I joined a different Health Plan called Molina on 3/1/10 and then I ended with them on 2/28/11 (and so on and so forth....). Now, let's say I have any number of Dates of Services (e.g., I caught the flu and was seen by my doctor on 4/22/09, I got a nail in my foot and had to go to Urgent Care on 2/14/10, etc., etc.).

As you can probably see, my flu DOS on 4/22/09 would be covered because I was eligible at the time (IEHP 1/1/09 through 12/31/09).
As you can probably also see, my nail in my foot on 2/14/10 would not be covered because I was not enrolled with a Health Plan at the time of my Date of Service (DOS) and therefore "Not Eligible".

So the tricky thing here is that my Eligibility IIf statement is working like this following screenshot (DATEFROM is the Date of Service):



So I think this example explains it best. So basically, if any of the Member's Eligibility Sequences covers the Date of Service, then I need Eligibility column to show as Eligible. I am planning to export the query results to Excel spreadsheet and then filter for "Not Eligible" (so we can perform Recovery processes).

I am thinking that this might possibly work:

I created a Step 2 query (bringing in * from Step 1 query as explained above) and added another field called Eligibility with a subsequent IIf statement as follows: Eligibility2: IIf(([CURRHIST]='C') Or ([CURRHIST]='H') And ([Eligibility]='Eligible'),"Eligible","Not Eligible")

That just results in the following screenshot:



Thank you in advance to anyone who can help me.