Error in Count on Report

  • Thread starter Thread starter H
  • Start date Start date
H

H

In my report I test to see if Time2< Time 1. If so, I
place an "*" next to the time. The "*" is in a separate
text box, called ESTimeFlag. This works fine.

I use VB code to check for the "*" and, if found, change
the Detail.BackColor = 255. Also, I want to count the
number of "*" so that I get a total of them on the final
page (report footer).

The count, however, gets added twice. I get double the
actual result. I moved the final count text box (which is
RedFlagCount) to the Page Footer and I see that what
happens is that on the first page of the report the count
is correct for the grand total, although none of the
detail lines of data on that fisrt page have any "*".
Looks like the routine is counting the entire report all
at once. Then, while going through the pages of the
report, the counter increases by one for each time a "*"
is encountered on a page. So, I get double the count.

What do I have wrong?

Here's what I have in the Detail (Format) build:

Dim RedFlagCounter As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer

If ESTimeFlag =*" Then
Detail.BackColor = 255
RedFlagCounter = RedFlagCounter + 1

Else
End If

RedFlagCount = RedFlagCounter

End Sub
 
What happens is that the Format event fires more than once, and the Print
event may fire more than once, when a Report runs. That is why accumulating
counts or totals in "code behind" is difficult... the difficulty can be
compounded if you preview and page backward and forward.

There is a Count builtin function, and Running Sum property that, if we are
careful, can do most of our counting for us, accurately.

In your detail section, create a calculated control with its recordsource
= Abs(Cint(Time2<Time1))
and set its running sum property to Over Group or Over All, whichever is
your preference.

If it is really important to you that you print in the page footer the
number on that page, you can calculate it there... just be sure that you are
using values that you don't _accumulate_ in code.

Larry Linson
Microsoft Access MVP
 
That's interesting. I didn't know how that worked. I
tried this:

Created a new field in the table and placed a "*" in it if
the time test was true. Then on the report, I just count
the number of those. Works, too. I will try your
suggestion also.

Another question, if I may:

Let's say, If Time2<Time1 then don't count that row's
records in the totals. I use the time data to calculate
totals and average times in final report result. But, I
use this time test to flag data that doesn't conform to
the expected result. (Time2 always needs to be more than
Time1, in other words. If not, I know something's wrong
in the imported data.)

If the test is true, what's the best way to code this so
that in the final report I don't count this time data
(which is in seconds and I later convert it to hh:nn:ss).
I know I could add a field in a table to move the
suspicious time data to it, and since I already flag the
record with a "*", and then I could total the suspicious
time data, substract the flag # of records and do some
more calculations to get a true total. But, maybe there
is a more efficient way?

Note: I don't want to delete the record. I need it
for 'manual' reference so that it can be researched and
checked. I already flag it and turn the row's background
Red so that it shows up on the report. I just don't want
to count it in the totals.

Thanks for your assistance.
 
Back
Top