running count of certain records based of criteria/conditions

[news.microsoft.com]

Hi folks,

I'm trying yo keep a running count of only certain records in a report.

Here's the situation, I have a report with say 50 records. One of the fields
is "referral date". I want to have a running sum of the number of these 50
records that have a "referral date" within a certain range. To set the date
range, I created a form with 2 textboxes, "startdate" and "stopdate".

I know how to use these dates as a filter to only show records in this
range, but I just want a count of the number of records in this range.

I tried using the Count function with the dates as a filter, but received
errors.

Any ideas?


thanks

 

[Marshall Barton]

Hi folks,

I'm trying yo keep a running count of only certain records in a report.

Here's the situation, I have a report with say 50 records. One of the fields
is "referral date". I want to have a running sum of the number of these 50
records that have a "referral date" within a certain range. To set the date
range, I created a form with 2 textboxes, "startdate" and "stopdate".

I know how to use these dates as a filter to only show records in this
range, but I just want a count of the number of records in this range.

I tried using the Count function with the dates as a filter, but received
errors.

Add an invisible text box named txtInRange and set its
expression to:
=IIf([refreral date] Between Forms!theform.startdate AND
Forms!theform.stopdate, 1, 0)

Then, set the counting text box to the expression:
=txtInRange
and saet its RunningSum property to Over Group (or Over All)

To avoid displaying the counting text box when the data is
out of the range, use a little code in the Format event
procedure of the section containing these text boxes:
Me.countingtextbox.Visible = (txtInRange = 1)