Help with DateDiff and Count Expression

[Dave]

I want to calculate aging for the number of bug reports
that have not been closed. The final report would look
something like this:

BUG AGING
0-7 Days 8-14 Days 15-21 Days Etc...
Bugs open 2 10 15

The criteria is DateClosed is Null.

I've managed to get the number of days for each BugID
using:

FIELD: Days Open: DateDiff("d",[DateOpened],Date())
Table: BugLog
TOTAL: Expression

I can't figure out how to get the record counts (2, 10,
15, Etc.) for each Bug Aging category (0-7, 8-14,...) I
tried:

FIELD: 0-7 Daysays Open
TOTAL: Count
CRITERIA: =<7

and something alltogether differnt using a sample for Date
()-nnn in Access Help:
Field: 0-7 Days: DateOpened
Table: BugLog
Total: Count
Criteria: Date()-7 AND Is Null[DateClosed], which didn't
give any errors but also did not give any results.


Help anyone?

 

[Chris]

It might be better to use weeks (7 day periods) rather
that days.
Change your existing formula

Days Open: DateDiff("d",[DateOpened],Date())
to
Period: int(DateDiff("d",[DateOpened],Date())/7)

This will give you a number 0...n which you can then group
on, count by and sort on.

 

[Dave]

Thanks mate, but can ya help me get the count syntax
right, my attempts don't put out any data.

Field: <1 Week: (DateDiff("d",[DateOpened],Date())/7)
Table: <nothin>
Total: Count
Criteria: <0

Field: 1 Week: (DateDiff("d",[DateOpened],Date())/7)
Table: <nothin>
Total: Count
Criteria: =1

Field: 2 Weeks: (DateDiff("d",[DateOpened],Date())/7)
Table: <nothin>
Total: Count
Criteria: =2

Even if the above did work will the code have to be
duplicated forever - the way my place works bugs can be
out there years!

Cheers.
Dave