Help with DateDiff and Count Expression

  • Thread starter Thread starter Dave
  • Start date Start date
D

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 Days:Days 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?
 
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.
 
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
 
Back
Top