Counter Equals more than it should

[J]

I have the following code and for some reason the counter variable
increments by 2 each time rather than 1. Myself and a friend have
looked at it but cannot figure out the problem, any help would be
appreciated with this. Text94 is a Yes/No Field if that makes any
difference

Dim c1 As Integer 'Global Variables
Dim c2 As Integer
Dim c3 As Integer

-----------------------------------------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


If Text24.value = 100 And Text94.value = -1 Then
c1 = c1 + 1

Else
If Text24.value = 100 And Text94.value = 0 Then
c2 = c2 + 1

Else
If Text24.value > 0 And Text24.value < 100 Then
c3 = c3 + 1

End If
End If
End If

End Sub
-----------------------------------------------------------------------------------------------------------------
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As
Integer)
Text67.value = CStr(c1)
Text68.value = CStr(c2)
Text85.value = CStr(c3)

End Sub

 

[hellboy_ga]

hmm...Why do you have two "Elses"?...

The body of the "Private Sub Detail_Format(Cancel As Integer,
FormatCount As Integer) " should be -
----------------------------------------
If Text24.value = 100 And Text94.value = -1 Then
c1 = c1 + 1

ElseIf Text24.value = 100 And Text94.value = 0 Then
c2 = c2 + 1

ElseIf Text24.value > 0 And Text24.value < 100 Then
c3 = c3 + 1

End If
-------------------------

Also which counter is increasing by 2? You have three counters (c1, c2,
c3)

hope it helps

 

[hellboy_ga]

Also for "ReportFooter_Format", I suggest you use -

Text67.Text = c1 etc.

when displaying values into the text box. That way, you don't have to
convert c1, c2, c3 to String.

 

[Kevin K. Sullivan]

Section_Format events can occur multiple times before a section is
printed. For instance, if your report displays "Page x of y", the
entire report must be formated before y is deduced. Any section that
references y will need to be re-formated. Access tells you which pass
through the format this is via the FormatCount variable. My guess is
that you want your counter math to happen only once per section. If
that is the case, wrap your Detail_Format code in:


If FormatCount = 1 Then
'do your counter math in here, once per detail section
Else
'this code runs on repeated passes
End If


HTH,

 

[Marshall Barton]

I have the following code and for some reason the counter variable
increments by 2 each time rather than 1. Myself and a friend have
looked at it but cannot figure out the problem, any help would be
appreciated with this. Text94 is a Yes/No Field if that makes any
difference

Dim c1 As Integer 'Global Variables
Dim c2 As Integer
Dim c3 As Integer

-----------------------------------------------------------------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


If Text24.value = 100 And Text94.value = -1 Then
c1 = c1 + 1

Else
If Text24.value = 100 And Text94.value = 0 Then
c2 = c2 + 1

Else
If Text24.value > 0 And Text24.value < 100 Then
c3 = c3 + 1

End If
End If
End If

End Sub
-----------------------------------------------------------------------------------------------------------------
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As
Integer)
Text67.value = CStr(c1)
Text68.value = CStr(c2)
Text85.value = CStr(c3)

End Sub

I think I will state it more strongly than Kevin. There is
no way to reliably use event procedures to calculate a total
across multiple records in a report. The sections in a
report are processed as many times and in whatever order
Access needs to format the report to your settings.

You shoould use aggregate functions in the header/footer to
calculate totals.

If your data is arranged in such a way (unusual) that you
can not use an aggrgate function, then use a RunningSum text
box to do the calculation.

 

[J]

What way would it need to be arranged so that an aggregate function
couldn't be used? Also, how could I implement a running sum text box??
I would assume I'd need an IIF statement?

 

[Marshall Barton]

What way would it need to be arranged so that an aggregate function
couldn't be used? Also, how could I implement a running sum text box??
I would assume I'd need an IIF statement?

The first rule of aggregate functions is that they only
operate on fields in the record source table query. That
means that you can not Count/Sum your text24 or text94
values. However, if those text boxes are bound to fields in
the record source table/query, you can use those fields
instead of the controls. E.g. let's say you have an
AmountDue text box that calculates the amount due using the
expression:
=Quantity * Price
You can not have a grand total text box expression
=Sum(AmountDue)
Instead you would need to use the expression:
=Sum(Quantity * Price)
You'll have to explain what your text box's COntrolSource is
before I can determine if/how to use an aggregate function.

To use a running sum text box, add a text box named txtRunC1
to the detail section using the expression:
=IIf(Text24 = 100 And Text94 = -1, 1, 0)
and set its RunningSum property to Over All. Then a text
box in the report footer section can display the result by
using the expression:
=txtRunC1

 

[J]

My text box control source is located in a query that takes information
from a single table and populates the report.

 

[Marshall Barton]

My text box control source is located in a query that takes information
from a single table and populates the report.

As long as the text boxes are bound to fields in the record
source, you can use an aggregate function. You did not tell
me the field names that are bound to which text box so I can
only generalize again. Try using this kind of expression in
a report footer text box:

=Sum(IIf([field24] = 100 And [field94] = -1, 1, 0))