Count how many records I have between dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a report that show me how many New Referrals I have outstanding, (I
already have a total field on the report) and I want to show how many of
these are:

Within 2 working days.
Between 2-5 working days.
Over 5 working days.

I am stumped as to where to start.

Thanks
 
I would create a table of ranges so the number of days wouldn't be hard-coded
into any query.
tblDayRanges
MinDays MaxDays DayTitle
-1 2 2 Working Days
2 5 2 to 5 Working Days
5 99999 More than 5 working Days

Then create a totals query with your table, tblDayRanges, and the
WorkingDays function:

Field: WDays: WorkingDays([UnnamedDateFld],Date())
Criteria: >MinDays AND <=MaxDays

Group by the fields from tblDayRanges and count some other field. This
should give you the number of records in each range. Use this as the record
source of a subreport.
 
Back
Top