count based on date parameters

[DBAFrog]

In have a report that requires a count of totals based on 30, 60, 90 120 days. So my report ending June 30 will contain approx 2500 records. I then need to count the number of records in the last 30 days, then the 31 to 60 day, then 61 to 90, etc.
So, I have a my date variables auto calculate based on @end_date variable.
i.e. @end_date is set to DATE(2004,6,30), so @minus_30 is
if day({@End Date})=31 then
DateAdd("d", -31, {@End Date})
else
DateAdd("d", -30, {@End Date})
and @minus_60 is the same with =31 set to =61 and so on.
But...
when I try to set my @count_30 to be
NumberVar Count30:=0;
if {REFERRAL.REFERRAL_DATE} < {@end_date} and {REFERRAL.REFERRAL_DATE} > {@minus_30}
Then Count30 := Count30 + 1

it doesn't count right ( or at all ).

I need some help with my syntax for this 'between' type stmt.

thanks,

Frog

------------------
Curious by Nature,
Linux by Design

 

[lbass]

Why not create a formula to group on:

if {table.date} in {?enddate}-30 to {?enddate} then "0 to 30" else
if {table.date} in {?enddate}-60 to {?enddate}-31 then "31 to 60" else
if {table.date} in {?enddate}-90 to {?enddate}-61 then "61 to 90" else
if {table.date} in {?enddate}-120 to {?enddate}-91 then "91 to 120" else
"120+"

Then insert summaries on your detail fields and suppress the details.

If you can't group the data for some reason, use the running total expert with a condition like:

{table.date} in {?enddate}-90 to {?enddate}-61

...in the evaluate based on a formula section.

-LB