Grouping for an Aging Report (Access 2K)

[CharlieT]

Hi folks,
I wouldn't be surprised if this were a simple matter that I'm just overlooking, but I've tried everything I can think of and haven't yet been successful.
I want to create statements, with outstanding invoices summed by date (0-30 days old, 31-60, 61-90, over 90). I've created the query with the DaysOpen calculated field, but when I try to create a crosstab query with the info, I get each individual elapsed time as its own column heading rather than the grouping I want. I found an example to group the days by month, but that isn't much help to me.

Can anyone lend a hand?

Thanks much.
CharlieT

[Francois]

Add a column to your query with the following expression
iif(Int([DaysOpen]/30)>3,3,Int([DaysOpen]))
and group on that column.
Maybe you will have to add 1 to DaysOpen to put the 30,60 and 90 in the right category.(I haven't test it)
iif(Int(([DaysOpen]+1)/30)>3,3,Int(([DaysOpen]+1)/30))

[CharlieT]

Francois,
Thanks for your reply. I started out using your idea, and then refined it to the following:

IIf(Int([DaysOpen])<30,30,IIf(Int([DaysOpen]) Between 31 And 60,60,IIf(Int([DaysOpen]) Between 61 And 90,90,120)))

It was a big challenge to get the syntax right, but it worked!.

Thanks again for your help.

Charlie T.

[HansV]

If DaysOpen is always positive, you can group on the expression

Partition([DaysOpen],1,90,30)

But if DaysOpen = 0 occurs, it will be put in a separate category. The Partition function can't handle negative numbers, but I suppose DaysOpen is non negative.