Need IIf statement to determine Member Eligibility please

[LukeBourke]

Got it, but still doesn't solve the problem with "-1"??

[June7]

-1 means True. You can convert the -1 to "Eligible" with an expression in a textbox, or, if you prefer, here is my query revised to show text instead of -1

SELECT dbo_RVS_CLAIM_MASTERS.MEMB_KEYID, IIf([DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()), "Eligible", "Not Eligible") AS Eligibility
FROM dbo_RVS_MEMB_HPHISTS RIGHT JOIN dbo_RVS_CLAIM_MASTERS ON dbo_RVS_MEMB_HPHISTS.MEMB_KEYID = dbo_RVS_CLAIM_MASTERS.MEMB_KEYID
GROUP BY dbo_RVS_CLAIM_MASTERS.MEMB_KEYID, [DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date())
HAVING ((([DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()))=True));

I have not downloaded your revised db. Is the query returning records correctly?

[LukeBourke]

I am going to attach another sample DB (version 3) (please run Q_test Memb Eligibility Step 2)

Notice:
1. MEMBNAME: ONE, PERSON DATEFROM 8/2/10 (none of the Eligibility Histories cover that Date of Service (1/1/10 to 6/30/10 is before 8/2/10 and next one is 9/1/10 to 3/31/11 which is after 8/2/10))
2. MEMBNAME: TWO, PERSON DATEFROM 12/2/12 (none of the Eligibility Histories cover that Date of Service (12/31/12 to 6/30/13 is after 12/2/12 and so is 7/1/13 to current))

If the Member is Not Eligible in any of the Eligibility Histories, all records should say "Not Eligible".... Please let me know if you have any questions.

I noticed if you take out the True Criteria in the [DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()), it goes back to the original problem (Eligible for the truly Eligible record, but Not Eligible for all other records for same member with same DATEFROM and more than 1 Eligibility History).....

And why are we doing a Nz (Null to zero)? If the OPTHRUDT is Null, then that denotes the current date and should remain Null (anyway, it isn't making those fields Null or Zero)?

[June7]

The Nz() isn't changing data in the table but it is handling the null by using the current date in lieu of null. Don't use it and see what happens. Do you want to exclude the records where OPTHRUDT is null?

So I had the rule backwards? Change the query to pull only the FALSE records then join to table.

[LukeBourke]

OK, thx for clarifying what the Nz is doing.

As far as changing the [DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()) criteria to False in Step 1, that would only result in "Not Eligible" to show up (and subsequently, all show up in Step 2 as "Not Eligible")?? I tried to put True or False as the criteria, but that results in Step 2 duplicating the records.....

(e.g., ONE, PERSON with DATEFROM of 8/2/10--1 record with DATEFROM 8/2/10 OPFROMDT 1/1/09 OPTHRUDT 12/31/09 Eligibility Eligible, repeat that pattern 5 times for that member's 5 Eligibility Sequences, then repeat that pattern 5 more times but showing "Not Eligible")

Remember, I need it to pull "Eligible" if the DATEFROM is Between any of the OPFROMDT and OPTHRUDT date ranges (and if so, show "Eligible" for all the records). I need it pull "Not Eligible" if the DATEFROM is not Between any of the OPFROMDT and OPTHRUDT date ranges (and if so, show "Not Eligible" for all the records). For all the records where the MembID and the DATEFROM are the same, that is.

[June7]

Last paragraph of your post is somewhat confusing but maybe I am finally catching on. Need to consider DATEFROM in the grouping?

Q_test Memb Eligibility Step 1
SELECT dbo_RVS_CLAIM_MASTERS.MEMB_KEYID, dbo_RVS_CLAIM_MASTERS.DATEFROM, IIf([DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()),"Eligible","Not Eligible") AS E
FROM dbo_RVS_MEMB_HPHISTS RIGHT JOIN dbo_RVS_CLAIM_MASTERS ON dbo_RVS_MEMB_HPHISTS.MEMB_KEYID = dbo_RVS_CLAIM_MASTERS.MEMB_KEYID
GROUP BY dbo_RVS_CLAIM_MASTERS.MEMB_KEYID, dbo_RVS_CLAIM_MASTERS.DATEFROM, IIf([DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()),"Eligible","Not Eligible")
HAVING ((Not (IIf([DateFrom] Between [OPFROMDT] And Nz([OPTHRUDT],Date()),"Eligible","Not Eligible"))="Eligible"));

Then join query to table on member and DATEFROM fields.

SELECT dbo_RVS_CLAIM_MASTERS.*, Nz([Q_test Memb Eligibility Step 1].E, "Eligible") AS Eligibility
FROM [Q_test Memb Eligibility Step 1] RIGHT JOIN dbo_RVS_CLAIM_MASTERS ON ([Q_test Memb Eligibility Step 1].MEMB_KEYID = dbo_RVS_CLAIM_MASTERS.MEMB_KEYID) AND ([Q_test Memb Eligibility Step 1].DATEFROM = dbo_RVS_CLAIM_MASTERS.DATEFROM);

The sample data results in all Claim_Masters records showing Not Eligible for each member/datefrom combination.

[LukeBourke]

I have found a much simpler way to do this (please see attached DB), but I need help with the simple-looking VBA function.

If you can take a quick look at the Q_test results in my attached DB, you will notice this:

I noticed the last 5 records (last 2 claim numbers) are actually wrong (weird, thought it was working based on ones before that showing up correctly).

Please see the attached DB (the Claim number 987654321 for TWO, PERSON with DATEFROM of 12/2/12 is before both of the Eligibility Histories so it should show up as "Not Eligible", not "Eligible" like it is showing up now....also, the Claim number 522334411 for THREE, PERSON with DATEFROM of 8/7/13 is current for the last Eligibility History that shows as OPFROMDT of 7/1/13 and OPTHRUDT of Null meaning that it is Current).

Please post up corrected DB or code to change.