Count how many records I have between dates

[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

 

[Guest]

Have you found the calculation for working days? If not, there is one at
http://www.mvps.org/access/datetime/date0006.htm.

Then you need to define "within 2 working days". Is this from today?

 

[Guest]

Hi Duane,

yes its from today.

Thanks

Have you found the calculation for working days? If not, there is one at
http://www.mvps.org/access/datetime/date0006.htm.

Then you need to define "within 2 working days". Is this from today?

 

[Guest]

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.