Counting Records in a date range

  • Thread starter Thread starter Joyce
  • Start date Start date
J

Joyce

Thank you Marsh for your help. Yes that works for me but
now I have another problem. This report I'm working with
is a monthly production report. I originally had the
report open first promting for a start and end date based
on date received. So for instance in Feb, the user would
key 02/01/04 to 02/29/04 and get all the cases, sorted by
case type for that time.

The problem is that the cases received in January but
resolved in February aren't being counted. So I thought I
should take the initial date search criteria out and just
list in the report

Cases Received =Abs(Sum(DateReceived Between #02/01/04#
And #02/29/04#))

Cases Resolved =Abs(Sum(DateResolved Between #02/01/04#
And #02/29/04#))

This is giving me the data I need, but the last bit of
information on my report is how many cases were resolved
within the establsihed turnaround time. Now when I
genterat my report, it calculates turnaround time year to
date, I need it to be just between Between #02/01/04# And
#02/29/04#.

Here is my code for determining cases closed within the
established turnaround time

=Count(IIf([TA2004]<=[TurnaroundDays],1,Null))

Is there a way I can limit the results to just cases
resolved from 02/01/04 to 02/29/04 in the above code?
Again, thank you for your help
 
Joyce said:
Thank you Marsh for your help. Yes that works for me but
now I have another problem. This report I'm working with
is a monthly production report. I originally had the
report open first promting for a start and end date based
on date received. So for instance in Feb, the user would
key 02/01/04 to 02/29/04 and get all the cases, sorted by
case type for that time.

The problem is that the cases received in January but
resolved in February aren't being counted. So I thought I
should take the initial date search criteria out and just
list in the report

Cases Received =Abs(Sum(DateReceived Between #02/01/04#
And #02/29/04#))

Cases Resolved =Abs(Sum(DateResolved Between #02/01/04#
And #02/29/04#))

This is giving me the data I need, but the last bit of
information on my report is how many cases were resolved
within the establsihed turnaround time. Now when I
genterat my report, it calculates turnaround time year to
date, I need it to be just between Between #02/01/04# And
#02/29/04#.

Here is my code for determining cases closed within the
established turnaround time

=Count(IIf([TA2004]<=[TurnaroundDays],1,Null))

Is there a way I can limit the results to just cases
resolved from 02/01/04 to 02/29/04 in the above code?
Click to expand...

Let's not start a new thread for follup questions, just post
a reply to my last answer. This will keep the relevent
information in one place so we don't have to chase through
tons of other posts to see what ground has already been
covered.

The Count(IIf( needs to include the date range as well:

=Count(IIf([TA2004]<=[TurnaroundDays] And DateResolved
Between #02/01/04# And #02/29/04#, 1, Null))

You can use parameter prompts in report control expressions
to avoid hard coding the the dates (and changing them every
month):

=Count(IIf([TA2004]<=[TurnaroundDays] And DateResolved
Between [Start Date] And [End Date], 1, Null))

Rather than using prompts, it would be better to use a form
where the users can enter the dates into text boxes:

=Count(IIf([TA2004]<=[TurnaroundDays] And DateResolved
Between Forms!theform.txtStartDate And
Forms!theform.txtEndDate, 1, Null))
 
Back
Top