2 Dates NOT in the same quarter

[ericb1]

Perhaps I'm not thinking about this correctly, but I have a table with 2 date fields in it, and I'm trying to figure out if they're not in the same date quarter.

A record is outstanding if the 2 dates are not in the same calendar quarter, and I'm trying to figure out a formula to do this. So far I have the following, but I don't think it's correct:

IF
({Claim_Releases.release_date} = Calendar1stQtr AND {Claims.Release_Approval_Date} <> Calendar1stQtr) OR
({Claim_Releases.release_date} = Calendar2ndQtr AND {Claims.Release_Approval_Date} <> Calendar2ndQtr) OR
({Claim_Releases.release_date} = Calendar3rdQtr AND {Claims.Release_Approval_Date} <> Calendar3rdQtr) OR
({Claim_Releases.release_date} = Calendar4thQtr AND {Claims.Release_Approval_Date} <> Calendar4thQtr) THEN
"CLAIM OUTSTANDING"

ELSE

"CLAIM NOT OUTSTANDING"

When I search through the data, I find records that say they're "OUTSTANDING" but the dates appear in the same quarter. Any help is greatly appreciated, thanks!

 

[lbass]

Try:

IF ({Claim_Releases.release_date} in Calendar1stQtr AND
not({Claims.Release_Approval_Date} in Calendar1stQtr)) OR
({Claim_Releases.release_date} in Calendar2ndQtr AND
not({Claims.Release_Approval_Date} in Calendar2ndQtr)) OR
({Claim_Releases.release_date} in Calendar3rdQtr AND
not({Claims.Release_Approval_Date} in Calendar3rdQtr)) OR
({Claim_Releases.release_date} in Calendar4thQtr AND
not({Claims.Release_Approval_Date} in Calendar4thQtr)) THEN
"CLAIM OUTSTANDING"
ELSE
"CLAIM NOT OUTSTANDING"

-LB

 

[ericb1]

Thanks LB, that looks right, but when I spot check the data, I see it's not correct: there's several records where the formula isn't holding true, for example:

7/1/2004 - 12/22/2004 - CLAIM NOT OUTSTANDING

but 7/1/04 is in the 3rd qt, and 12/22/04 is in the 4th qt, so it should say CLAIM OUTSTANDING.

Anyway, I have the report grouped by quarter, based on one of the dates, so rather than use "Calendar1sQtr", etc., I really want to use the group, but I keep getting an error that the Quarter group must be a DATETIME. Any way I can get around this? Here's what I did:

IF ({Claim_Releases.release_date} in GroupName ({Claim_Releases.release_date}, "quarterly") AND
not({Claims.Release_Approval_Date} in GroupName ({Claim_Releases.release_date}, "quarterly"))) THEN
"CLAIM OUTSTANDING"
ELSE
"CLAIM NOT OUTSTANDING"

Even if I wrap the group in a "DATETIME" it still gives me the same error.

Any help is greatly appreciated, thanks LB, you're the best!

 

[lbass]

You should stick with the formula I suggested. It should work fine with your quarter group. I can't think of any reason why you would get any incorrect records. I would double check the formula again. Why don't you paste it into the thread?

-LB

 

[Madawc]

What about Datepart("q", {Claim_Releases.release_date}) <> Datepart("q", {Claims.Release_Approval_Date}) ?

This doesn't allow for two dates in the same quarter of different years - that would need or Year({Claim_Releases.release_date}) <> Year({Claims.Release_Approval_Date})



Madawc Williams (East Anglia, UK)

 

[ericb1]

Thanks to both of you! Madawc's formula seems to work correctly, but for the life of me, I don't understand why the first formula doesn't work right.

For other's looking for a similiar formula, here's what I have: a simple report, based on a table with 2 dates, release_date, and release_approval_date. A record is outstanding if the 2 dates are not in the same claendar quarter. That's all.

I'm not sure why one formula works and the other doesn't, except that perhaps b/c my date fields were both "DATETIME" formatted, that might screw things up.

Anyway, thanks again to both of you!