Ah. The basic problem here is one of timing. Your selection criteria are being evaluated
before @Future is. So, your @Future value is actually returning the results for the previous record.
The simplest solution is to simply combine the functionality of the two formulas. So, it might look like:
IF {APPT.APPT_DATE} >= dateadd("m", -13, CurrentDate)
AND {APPT.APPT_DATE} <= CurrentDate
THEN
IF Month(CurrentDate) = 1 THEN
IF Month({APPT.APPT_DATE}) = 12
AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 2
THEN "PAST DUE"
ELSE ""
ELSE
IF Month({APPT.APPT_DATE}) = Month(CurrentDate) - 1
AND Year({APPT.APPT_DATE}) = Year(CurrentDate) - 1
THEN "PAST DUE"
ELSE ""
ELSE ""
Although, honestly, I'm starting to question your whole methodology. I think that you are starting to pile up too many discrete functions, and their interactions are no longer entirely clear. Also, the differences between a straight DateDiff on CurrentDate and the Month and Year comparison for @PastDue is, I think, creating some overlap. Let's try a different approach.
In each record, let's put a formula, call it @ApptStatus that looks like:
Local DateVar CalcDate := CurrentDate - Day(CurrentDate) + 1;
//This function returns the first day of the current month.
SELECT DateDiff("m",{Appt.ApptDate},CalcDate)
CASE Is > 13 : 1 //Equivalent to "Old"
CASE 13 : 2 //Equivalent to "Past Due"
CASE Is < 1 : 4 //Equivalent to "Future"
DEFAULT : 3 //Equivalent to "Current";
Now, in the group footer, we put another formula, call it @ApptDesc, that looks like:
("Old","PAST DUE","Current","Future")[Maximum(@ApptStatus,{Appt.PatientID})]
//This will return the string corresponding to the latest appointment status.
In the suppression criteria of your group footer, you can put a formula like:
Maximum(@ApptStatus,{Appt.PatientID}) <> 2
As another note, I would simply add to your Select Criteria a restriction where ApptDate >= DateAdd("m",-14,CurrentDate). This will prevent a lot of irrelevant data from coming in.
IP Logged
