Seemingly simple operation producing Wildly Crazy results

[Richard Hollenbeck]

For example, one college course has only 24 students in it, but the
following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs. When
it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs. The actual
number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and 4 Fs. I can't find
anything wrong with the code. Here it is:

Option Compare Database
Option Explicit

'global variables
Dim numOfA As Integer
Dim numOfB As Integer
Dim numOfC As Integer
Dim numOfD As Integer
Dim numOfF As Integer

Private Sub Report_Open(Can cel As Integer)
numOfA = 0
numOfB = 0
numOfC = 0
numOfD = 0
numOfF = 0
End Sub

Private Sub GroupHeader1_Fo rmat(Cancel As Integer, FormatCount As Integer)
If txtAvg >= 0.92 Then
numOfA = numOfA + 1
End If

If txtAvg >= 0.82 And txtAvg < 0.92 Then
numOfB = numOfB + 1
End If

If txtAvg >= 0.72 And txtAvg < 0.82 Then
numOfC = numOfC + 1
End If

If txtAvg >= 0.62 And txtAvg < 0.72 Then
numOfD = numOfD + 1
End If

If txtAvg < 0.62 Then
numOfF = numOfF + 1
End If
End Sub


Private Function getAay() As Integer
getAay = numOfA
End Function

Private Function getBee() As Integer
getBee = numOfB
End Function

Private Function getCee() As Integer
getCee = numOfC
End Function
Private Function getDee() As Integer
getDee = numOfD
End Function

Private Function getEff() As Integer
getEff = numOfF
End Function

[Roald Oines]

Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
: For example, one college course has only 24 students in it, but the
: following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs.
: When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs. The
: actual number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and 4 Fs.
: I can't find anything wrong with the code. Here it is:
:
: Option Compare Database
: Option Explicit
:
: 'global variables
: Dim numOfA As Integer
: Dim numOfB As Integer
: Dim numOfC As Integer
: Dim numOfD As Integer
: Dim numOfF As Integer
:
: Private Sub Report_Open(Can cel As Integer)
: numOfA = 0
: numOfB = 0
: numOfC = 0
: numOfD = 0
: numOfF = 0
: End Sub
:
: Private Sub GroupHeader1_Fo rmat(Cancel As Integer, FormatCount As
: Integer) If txtAvg >= 0.92 Then
: numOfA = numOfA + 1
: End If
:
: If txtAvg >= 0.82 And txtAvg < 0.92 Then
: numOfB = numOfB + 1
: End If
:
: If txtAvg >= 0.72 And txtAvg < 0.82 Then
: numOfC = numOfC + 1
: End If
:
: If txtAvg >= 0.62 And txtAvg < 0.72 Then
: numOfD = numOfD + 1
: End If
:
: If txtAvg < 0.62 Then
: numOfF = numOfF + 1
: End If
: End Sub
:
:
<snip>

So it's multiplying the correct answers by two? I wonder...how many
times does the format event fire for this header?
--
Please remove the under_scores if replying by mail.

[Richard Hollenbeck]

Yes it's multiplying it by 2 initially, but then, when I print the report,
it multiplies by two again.

"Roald Oines" <r_*******@bres nan.net> wrote in message
news:Us******** ************@br esnan.com...

Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
: For example, one college course has only 24 students in it, but the
: following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs.
: When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs. The
: actual number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and 4 Fs.
: I can't find anything wrong with the code. Here it is:
:
: Option Compare Database
: Option Explicit
:
: 'global variables
: Dim numOfA As Integer
: Dim numOfB As Integer
: Dim numOfC As Integer
: Dim numOfD As Integer
: Dim numOfF As Integer
:
: Private Sub Report_Open(Can cel As Integer)
: numOfA = 0
: numOfB = 0
: numOfC = 0
: numOfD = 0
: numOfF = 0
: End Sub
:
: Private Sub GroupHeader1_Fo rmat(Cancel As Integer, FormatCount As
: Integer) If txtAvg >= 0.92 Then
: numOfA = numOfA + 1
: End If
:
: If txtAvg >= 0.82 And txtAvg < 0.92 Then
: numOfB = numOfB + 1
: End If
:
: If txtAvg >= 0.72 And txtAvg < 0.82 Then
: numOfC = numOfC + 1
: End If
:
: If txtAvg >= 0.62 And txtAvg < 0.72 Then
: numOfD = numOfD + 1
: End If
:
: If txtAvg < 0.62 Then
: numOfF = numOfF + 1
: End If
: End Sub
:
:
<snip>

So it's multiplying the correct answers by two? I wonder...how many
times does the format event fire for this header?
--
Please remove the under_scores if replying by mail.

[Roald Oines]

Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
: Yes it's multiplying it by 2 initially, but then, when I print the
: report, it multiplies by two again.
:
: "Roald Oines" <r_*******@bres nan.net> wrote in message
: news:Us******** ************@br esnan.com...
:: Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
::: For example, one college course has only 24 students in it, but the
::: following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs.
::: When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs.
::: The actual number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and
::: 4 Fs. I can't find anything wrong with the code. Here it is:
:::
::: Option Compare Database
::: Option Explicit
:::
::: 'global variables
::: Dim numOfA As Integer
::: Dim numOfB As Integer
::: Dim numOfC As Integer
::: Dim numOfD As Integer
::: Dim numOfF As Integer
:::
::: Private Sub Report_Open(Can cel As Integer)
::: numOfA = 0
::: numOfB = 0
::: numOfC = 0
::: numOfD = 0
::: numOfF = 0
::: End Sub
:::
::: Private Sub GroupHeader1_Fo rmat(Cancel As Integer, FormatCount As
::: Integer) If txtAvg >= 0.92 Then
::: numOfA = numOfA + 1
::: End If
:::
::: If txtAvg >= 0.82 And txtAvg < 0.92 Then
::: numOfB = numOfB + 1
::: End If
:::
::: If txtAvg >= 0.72 And txtAvg < 0.82 Then
::: numOfC = numOfC + 1
::: End If
:::
::: If txtAvg >= 0.62 And txtAvg < 0.72 Then
::: numOfD = numOfD + 1
::: End If
:::
::: If txtAvg < 0.62 Then
::: numOfF = numOfF + 1
::: End If
::: End Sub
:::
:::
:: <snip>
::
:: So it's multiplying the correct answers by two? I wonder...how many
:: times does the format event fire for this header?

My point is that the format event can fire more than once if Access has
to return to previous sections for multiple formatting passes. This will
cause your counts to increment each time. You can use the Retreat event
to undo any changes you've done that you only want done once in the
section.

--
Please remove the under_scores if replying by mail.

[Richard Hollenbeck]

Since the results are normally displayed on the second page, perhaps the
format event fires a second time before I even see the data then again at
print time. I wonder if there is another event I could put this subroutine
into that will only fire once--period.

By the way, the text boxes that display this data are set to private
functions. For example, the txtA box is set to =getAay(), and the txtB box
is set to =getBee(), etc.:

Private Function getAay() As Integer
getAay = numOfA
End Function

Private Function getBee() As Integer
getBee = numOfB
End Function

etc.

"Roald Oines" <r_*******@bres nan.net> wrote in message
news:Us******** ************@br esnan.com...

Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
: For example, one college course has only 24 students in it, but the
: following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs.
: When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs. The
: actual number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and 4 Fs.
: I can't find anything wrong with the code. Here it is:
:
: Option Compare Database
: Option Explicit
:
: 'global variables
: Dim numOfA As Integer
: Dim numOfB As Integer
: Dim numOfC As Integer
: Dim numOfD As Integer
: Dim numOfF As Integer
:
: Private Sub Report_Open(Can cel As Integer)
: numOfA = 0
: numOfB = 0
: numOfC = 0
: numOfD = 0
: numOfF = 0
: End Sub
:
: Private Sub GroupHeader1_Fo rmat(Cancel As Integer, FormatCount As
: Integer) If txtAvg >= 0.92 Then
: numOfA = numOfA + 1
: End If
:
: If txtAvg >= 0.82 And txtAvg < 0.92 Then
: numOfB = numOfB + 1
: End If
:
: If txtAvg >= 0.72 And txtAvg < 0.82 Then
: numOfC = numOfC + 1
: End If
:
: If txtAvg >= 0.62 And txtAvg < 0.72 Then
: numOfD = numOfD + 1
: End If
:
: If txtAvg < 0.62 Then
: numOfF = numOfF + 1
: End If
: End Sub
:
:
<snip>

So it's multiplying the correct answers by two? I wonder...how many
times does the format event fire for this header?
--
Please remove the under_scores if replying by mail.

[Richard Hollenbeck]

Roald Oines,

I think I understand what you mean. Thanks. I'll go back and study the
retreat event to see what I can do. Thanks a lot! I'm not sure how this
will work, but now I have something new to study. After I try it I'll get
back to the group.

Rich

"Roald Oines" <r_*******@bres nan.net> wrote in message
news:XJ******** ************@br esnan.com...

Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
: Yes it's multiplying it by 2 initially, but then, when I print the
: report, it multiplies by two again.
:
: "Roald Oines" <r_*******@bres nan.net> wrote in message
: news:Us******** ************@br esnan.com...
:: Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
::: For example, one college course has only 24 students in it, but the
::: following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs.
::: When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs.
::: The actual number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and
::: 4 Fs. I can't find anything wrong with the code. Here it is:
:::
::: Option Compare Database
::: Option Explicit
:::
::: 'global variables
::: Dim numOfA As Integer
::: Dim numOfB As Integer
::: Dim numOfC As Integer
::: Dim numOfD As Integer
::: Dim numOfF As Integer
:::
::: Private Sub Report_Open(Can cel As Integer)
::: numOfA = 0
::: numOfB = 0
::: numOfC = 0
::: numOfD = 0
::: numOfF = 0
::: End Sub
:::
::: Private Sub GroupHeader1_Fo rmat(Cancel As Integer, FormatCount As
::: Integer) If txtAvg >= 0.92 Then
::: numOfA = numOfA + 1
::: End If
:::
::: If txtAvg >= 0.82 And txtAvg < 0.92 Then
::: numOfB = numOfB + 1
::: End If
:::
::: If txtAvg >= 0.72 And txtAvg < 0.82 Then
::: numOfC = numOfC + 1
::: End If
:::
::: If txtAvg >= 0.62 And txtAvg < 0.72 Then
::: numOfD = numOfD + 1
::: End If
:::
::: If txtAvg < 0.62 Then
::: numOfF = numOfF + 1
::: End If
::: End Sub
:::
:::
:: <snip>
::
:: So it's multiplying the correct answers by two? I wonder...how many
:: times does the format event fire for this header?

My point is that the format event can fire more than once if Access has
to return to previous sections for multiple formatting passes. This will
cause your counts to increment each time. You can use the Retreat event
to undo any changes you've done that you only want done once in the
section.

--
Please remove the under_scores if replying by mail.

[Brian]

"Roald Oines" <r_*******@bres nan.net> wrote in message
news:XJ******** ************@br esnan.com...

Richard Hollenbeck <ri************ ****@verizon.ne t> wrote:
: Yes it's multiplying it by 2 initially, but then, when I print the
: report, it multiplies by two again.
:

If you are using page numbering of the type "page n of m" then the entire
report is formatted twice: once to find out how many pages there are, and
then again with the correct page numbering.

I can't help feeling that this is not a smart way for you to proceed. If
you posted more information about what you are trying to achieve, someone
might have an alternative suggestion.

[Albert D. Kallal]

Just use the format count var to check this:

Private Sub GroupHeader1_Fo rmat(Cancel As Integer, FormatCount As Integer)

if FormatCount <> 1 then
exit sub
end if

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn

[Pieter Linden]

Is there a compelling reason *not* to put the (BETWEEN a AND b) =
<grade> in a query and then base your report on that?

[Marco Pagliero]

Yes, the Format event is difficult to handle.
Make a query with all fields of your table and these more fields:

AA: iif(txtAvg >= 0.92 , 1, 0)
BB: iif(txtAvg >= 0.82 And txtAvg < 0.92 , 1, 0)
CC: iif(txtAvg >= 0.72 And txtAvg < 0.82 , 1, 0)
DD: iif(txtAvg >= 0.62 And txtAvg < 0.72 , 1, 0)
FF: iif(txtAvg < 0.62, 1, 0)

Put this query under your report then define the fields in the
Report_Footer as:

=SUM(AA)
=SUM(BB)
=SUM(CC)
=SUM(DD)
=SUM(FF)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!