So I was recently tasked with creating a report of our donors. The parameters were: They must have given at least one gift this past fiscal year. And they must have given a minimum of $500 a year on five different fiscal years.

They don't have to be consecutive.

So I created a report that first groups based on Donor ID. Then groups on Gift Date (gifts are not guaranteed to have been entered into database in chronological order). Finally I group on Gift ID because they do gift splits which causes gifts to repeat.

I came up with the following formula. I should note our fiscal year starts July 1st. So I adjust for that.

whileprintingrecords;

global numbervar lastYear;

global numbervar giftCummulative;

global numbervar validYears;

global booleanvar GaveThisYear;

numbervar m = Month({AD_V_GIFT_HIST.GIFT_DATE});

numbervar y = Year({AD_V_GIFT_HIST.GIFT_DATE});

datevar d;



if m < 7 then

    y:= y-1;



d = date(y,7,1);



if d in {?CurrentFiscalYearRange} then 

    GaveThisYear := true;



if y <> lastYear then(

    lastYear:= y;

    if {@GiftCumMatches} then

        validYears:= validYears + 1;

    giftCummulative:= {AD_V_GIFT_HIST.GIFT_AMT} ;

)

else

    giftCummulative:=giftCummulative + {AD_V_GIFT_HIST.GIFT_AMT};

This formula runs in the group header of the GiftID group. So it only runs once per gift.

I have a reset formula that sets everything to zero or false and that runs every time the Donor ID changes.

So here's my problem.

At precisely record 107706 it errors out saying I need to define a year between 1 and 9999. On the line d=date(y-1,7,1).

When the formula editor opens up and shows the values of the different variables it shows a date of "2010-10-20 00:00:00" for Gift_Date. But Y and M both return zero.

I have no idea what is wrong with the code. Or why Year() would suddenly stop returning a value even though there clearly is one available.

Any suggestions?

Edited by levifiction - 05 Jul 2015 at 5:59am

Thank you for the reply. I figured out whythe year was returning zero.

I'm using Crystal format. So I forgot Crystal's assignment operator is (:=) not (=) so that's what happened there.

Why it only erred out only at record 107706 is also weird. It's like it had to wait until the report finished to report the error.

I also fixed another error so everything seems to be working. I can't prove it's right but at least I have a report that's returning data. xD

Edited by levifiction - 07 Jul 2015 at 8:26am