Need IIf statement to determine Member Eligibility please

[LukeBourke]

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 example:
EligibleFrom EligibleTo DOS(Date of Service) Current History Eligibility
1/1/09 12/31/09 4/22/09 H Eligible 'this one is correct
3/1/10 2/28/11 4/22/09 C Not Eligible 'this is also technically correct, but if any of the Eligibility
Sequences covered the DOS, then member is Eligible
and no need to Recover payment (big purpose of this).


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 makes sense to me, but throws me a "The expression you entered has a function containing the wrong number of arguments."???????


Thank you in advance to anyone who can help me.

[June7]

Use quote marks instead of apostrophes around C, H, Eligible.

Probably need parens around the OR criteria, unless "H" and "Eligible" go together. Parens are critical when mixing OR and AND operators.

Eligibility2: IIf(([CURRHIST]="C" Or [CURRHIST]="H") And [Eligibility]="Eligible","Eligible","Not Eligible")

[LukeBourke]

Didn't work at all (same results)....

I have been trying to wrap my head around this concept for a while, but I think it is the original IIf statement which is doing what I am telling it to do, but I am not sure how to handle the multiple sequences of member eligibility (there has to be a way to tell it: If the Member Name, the MemberID, the DateofBirth are all the same and any of the Eligibility values for those records say "Eligible", then make all records for that member "Eligible".

I have also been trying to get a formula to work in Excel 2010 to no avail??

[June7]

Criteria that is dependent on data in other records of same table requires subquery or domain aggregate function (DLookup is one) or VBA code. Example with DLookup:

SELECT *, Nz(DLookup("Eligibility","tablename","MemberID=" & [MemberID] & " AND Eligibility='Eligible'"),"Not Eligible") AS Status FROM tablename;

[ssanfu]

I created tables based on best guess -

Attachment 13328

wrote a UDF

Public Function GetCoverage(pPatient_FK As Long, pDOS As Date, pCURRHIST As String) As String
   Dim d As DAO.Database
   Dim r As DAO.Recordset
   Dim sSQL As String
   Dim tmp As String

   Set d = CurrentDb

   'se default return value
   GetCoverage = "Not Eligible"

   sSQL = "SELECT OPFROMDT, OPTHRUDT"
   sSQL = sSQL & " FROM dbo_RVS_MEMB_HPHISTS"
   sSQL = sSQL & " WHERE OPFROMDT <= #" & pDOS & "# AND"
   sSQL = sSQL & " OPTHRUDT >= #" & pDOS & "# AND"
   sSQL = sSQL & " PatientID = " & pPatient_FK
   sSQL = sSQL & " ORDER BY OPTHRUDT DESC;"

   Set r = d.OpenRecordset(sSQL)
   If Not r.BOF And Not r.EOF Then
      tmp = "Eligible"
      If pCURRHIST <> "C" And pCURRHIST <> "H" Then
         tmp = "Not Eligible"
      End If

      GetCoverage = tmp

   End If

   r.Close
   Set r = Nothing
   Set d = Nothing

End Function

This is the query

SELECT DISTINCT Patients.FName, Patients.LName, Visits.DOS, Visits.CURRHIST, Visits.Reason, GetCoverage([Patient_FK],[DOS],[CURRHIST]) AS Eligibility
FROM Patients INNER JOIN Visits ON Patients.Patient_PK = Visits.Patient_FK;

Results
Attachment 13329


Just an alternative.....

[LukeBourke]

Criteria that is dependent on data in other records of same table requires subquery or domain aggregate function (DLookup is one) or VBA code. Example with DLookup:

SELECT *, Nz(DLookup("Eligibility","tablename","MemberID=" & [MemberID] & " AND Eligibility='Eligible'"),"Not Eligible") AS Status FROM tablename;

So I am thinking like this:

SELECT Nz(DLookUp("Eligibility","Q_Claims Recovery Paid_RIOS_Summary_v2 Step 1","MEMBID=" & [MEMBID] & " AND Eligibility='Eligible'"),"Not Eligible") AS Status, *
FROM
(SELECT
dbo_RVS_CLAIM_MASTERS.MEMBID, dbo_RVS_CLAIM_MASTERS.MEMBNAME, dbo_RVS_CLAIM_MASTERS.PHCODE, dbo_RVS_CLAIM_MASTERS.CLAIMNO, dbo_RVS_CLAIM_MASTERS.PROVID, dbo_RVS_CLAIM_MASTERS.VENDOR, dbo_RV_VEND_MASTERS.VENDORNM, dbo_RV_VEND_MASTERS.STREET, dbo_RV_VEND_MASTERS.CITY, dbo_RV_VEND_MASTERS.STATE, dbo_RV_VEND_MASTERS.ZIP, dbo_RVS_CLAIM_MASTERS.DATEPAID, dbo_RVS_CLAIM_MASTERS.STATUS, dbo_RVS_CLAIM_MASTERS.BILLED AS Billed, dbo_RVS_CLAIM_MASTERS.CONTRVAL AS ContrVal, dbo_RVS_CLAIM_MASTERS.NET AS Net, dbo_RVS_CLAIM_MASTERS.DATEFROM, dbo_RVS_CLAIM_MASTERS.DATETO, dbo_RVS_CLAIM_MASTERS.CHECKNO, dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4, dbo_RV_VEND_MASTERS.TAXID, dbo_RVS_AUTH_MASTERS.AUTHNO, dbo_RVS_CLAIM_MASTERS.PLACESVC, dbo_RVS_MEMB_HPHISTS.OPFROMDT, dbo_RVS_MEMB_HPHISTS.OPTHRUDT, dbo_RVS_MEMB_HPHISTS.CURRHIST, IIf(([DATEFROM]<[dbo_RVS_MEMB_HPHISTS].[OPFROMDT]) Or ([DATEFROM]>[dbo_RVS_MEMB_HPHISTS].[OPTHRUDT]),"Not Eligible","Eligible") AS Eligibility
FROM
((((dbo_RV_VEND_MASTERS
INNER JOIN dbo_RVS_CLAIM_MASTERS ON dbo_RV_VEND_MASTERS.VENDORID = dbo_RVS_CLAIM_MASTERS.VENDOR)
LEFT JOIN dbo_RVS_CLAIM_MEMOFLDS ON dbo_RVS_CLAIM_MASTERS.CLAIMNO = dbo_RVS_CLAIM_MEMOFLDS.CLAIMNO)
LEFT JOIN dbo_RVS_AUTH_MASTERS ON dbo_RVS_CLAIM_MASTERS.AUTHNO = dbo_RVS_AUTH_MASTERS.AUTHNO)
INNER JOIN dbo_RVS_MEMB_COMPANY ON dbo_RVS_CLAIM_MASTERS.MEMB_KEYID = dbo_RVS_MEMB_COMPANY.MEMB_KEYID)
INNER JOIN dbo_RVS_MEMB_HPHISTS ON dbo_RVS_MEMB_COMPANY.MEMB_KEYID = dbo_RVS_MEMB_HPHISTS.MEMB_KEYID
WHERE
(((dbo_RVS_CLAIM_MASTERS.DATEPAID) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0))
AND ((dbo_RVS_CLAIM_MASTERS.STATUS)="9")
AND ((dbo_RVS_CLAIM_MASTERS.NET)<>0)
AND ((dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4) Not Like "*RC*"
AND (dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4) Not Like "*R$*"
AND (dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4) Not Like "*Refund*")
AND ((dbo_RV_VEND_MASTERS.TAXID) In ("330773865","450644640","455455436")))
ORDER BY
dbo_RVS_CLAIM_MASTERS.MEMBNAME, dbo_RVS_CLAIM_MASTERS.CLAIMNO) AS ['Q_Claims Recovery Paid_RIOS_Summary_v2 Step 2'];

Is this what you are meaning? When I selected the little down arrow of the Status column (which all records are blank in that column), a message saying "Syntax error in number in query expression 'MEMBID=[9 digit MEMBID number here] AND Eligibility='Eligible'." And then it won't close when I click OK (many times) and then another message saying "Date type mismatch in criteria expression." comes up??

Please let me know??

[June7]

Eligibility is not a field in a table or query object? Don't think your sql will work. I think domain aggregate functions must act on saved objects, the nested subquery is
not an object.

If you want to use nested subquery then don't use DLookup. Do a sql that returns only a single record for each each eligible member.

SELECT DISTINCT MEMBID, "Eligible" AS Eligibility FROM joined tables WHERE DATEFROM filter criteria ...";

Then join that SQL to the complete dataset. Every MEMBID record will have the Eligibility field. The field will show "Eligible" or be Null.


Is MEMBID a text not number field? Try:

"MEMBID='" & [MEMBID] & "' AND

[LukeBourke]

Yes MEMBID is a text field and Yes putting the single apostrophe got rid of that error in Datasheet View with results showing, but Status column had all records Null. And Eligibility is just the alias for the IIf statement so isn't it technically considered a field in a query object??

That is for syntax like this:

SELECT Nz(DLookUp("Eligibility","Q_Claims Recovery Paid_Summary_RIOS_v2 Step 1","MEMBID='" & [MEMBID] & "' AND Eligibility='Eligible'"),"Not Eligible") AS Status, *
FROM [Q_Claims Recovery Paid_Summary_RIOS_v2 Step 1];

So DLookup is being used on an object here instead of the SQL syntax from Step 1...

Let me try the DISTINCT method and then join that to the complete dataset and see what I get.....hold on.....

[June7]

I've never even attempted a domain aggregate function referencing a nested alias table. I always accepted that it would not work.

Also, I just noticed nested table has alias name ['Q_Claims Recovery Paid_RIOS_Summary_v2 Step 2'] (why are there apostrophes in the name?) but the DLookup references [Q_Claims Recovery Paid_RIOS_Summary_v2 Step 1].

[LukeBourke]

I just use an Alias Query name to make it noticeable that it is using a nested subquery (in Design View, I can see quickly the single apostrophes around the name).

Aliasing is just aliasing, I don't know too much about using domain aggregates on nested subqueries, but I do know that aliasing won't get in the way of it (if that would work).

I have to finish off one of my other projects (TEDIOUS....), but I am almost finished with that,

and then I will start working on the question I am asking you about.

My thinking is like this:

1. Use the DISTINCT and pull in only MEMBID and Eligibility fields for Step 1
2. Use my original query (the one that is showing duplicate records except for the OPFROMDT and OPTHRUDT which shows the member's eligibility history. That is what makes it not duplicate records, all other fields are the same. Oh, and the Eligibility field is different too because obviously, one of the eligibility histories covers the Date of Service and the other one doesn't or the other 2 or 3, etc. depending on how many eligibility history sequences they have don't)
3. Link both of those queries together in a 1-to-many relationship using my original Eligibility IIf statement but I need that IIf statement to say "If Null, run original IIf statement" (so a nested IIf statement). That way it will pick up the DISTINCE records from Step 1 and the same member's other eligibility history sequences and rerun the IIf statement on those records (because those ones would be Null).

Do you agree? Please share.

[June7]

If the domain aggregate function is supposed to pull value from the aliased subquery, then the name referenced in the domain aggregate must be the same as the alias subquery name. I would have thought the domain aggregate would error because it is not finding the object.

I don't understand what you are suggesting in 3. The final query can calculate with the Eligibility field: Nz([Eligibility], "Not Eligible")

[LukeBourke]

So how I name the 2 queries is put Step 1 and Step 2. Step 1 is all the tables needed to pull in the Member's Eligibility Histories and etc....Step 2 is just using that domain aggregate DLOOKUP function you gave me in the first SELECT and then FROM (original SELECT statement). If I don't put any alias at the end of the whole query, it automatically puts something weird like "AS %%%@Alias@%#". So I just put an Alias in there.

But I think you are telling me that I have to change the query name in the DLOOKUP function to be that same aliased name??

I don't understand what you are saying about "3. The final query can calculate with the Eligibility field: Nz([Eligibility], "Not Eligible")"?? The way I am looking at it is that the records from #1 will show Eligible for the most part and then I guess I could just make Step 2 link to Step 1 with the Eligibility field like this:

IIf([Eligibility] Is Null, IIf(([DATEFROM]<[dbo_RVS_MEMB_HPHISTS].[OPFROMDT]) Or ([DATEFROM]>[dbo_RVS_MEMB_HPHISTS].[OPTHRUDT]),"Not Eligible","Eligible")) but I don't know if that is the correct syntax??

I am sure you are far more experienced and smarter at this than I am so please advise me here.

[June7]

I suggested you do a DISTINCT query that returns only those MEMBID's that are Eligible following the example sql in post 7. Join that query to complete dataset with a join of "Include all records from {complete dataset}...". Every record will have the Eligibility field that shows either "Eligible" or null. In that final query, calculate with Eligibility field to show "Not Eligible" if it is null: Nz([Eligibility], "Not Eligible")

If you want to provide db for analysis, follow instructions at bottom of my post. Otherwise, we will just keep going in circles.

[LukeBourke]

OK, so I have my Step 1 bringing in all the complete data set. I have a Step 1a bringing in a DISTINCT list of members who are only "Eligible" and the SQL is this:

SELECT DISTINCT [Q_Claims Recovery Paid_Summary_RIOS_v2 Step 1].MEMBID, [Q_Claims Recovery Paid_Summary_RIOS_v2 Step 1].Eligibility
FROM [Q_Claims Recovery Paid_Summary_RIOS_v2 Step 1]
WHERE ((([Q_Claims Recovery Paid_Summary_RIOS_v2 Step 1].Eligibility)="Eligible"))
ORDER BY [Q_Claims Recovery Paid_Summary_RIOS_v2 Step 1].MEMBID;

I made my Step 2 Include all records from Step 1 and only those records from Step 1a where the joined fields are equal and put the Eligibility IIf statement to use Step 1. I don't know why the same fields are showing up multiple times (1 from each table for MembID and Eligibility)??

[June7]

I can't deal with this any more in this manner. I have to work with the data.