470,632 Members | 1,898 Online

# Seemingly simple operation producing Wildly Crazy results

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(Cancel As Integer)
numOfA = 0
numOfB = 0
numOfC = 0
numOfD = 0
numOfF = 0
End Sub

Private Sub GroupHeader1_Format(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
Nov 13 '05 #1
15 1847 Richard Hollenbeck <ri****************@verizon.net> 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(Cancel As Integer)
: numOfA = 0
: numOfB = 0
: numOfC = 0
: numOfD = 0
: numOfF = 0
: End Sub
:
: Private Sub GroupHeader1_Format(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?
--
Nov 13 '05 #2
Yes it's multiplying it by 2 initially, but then, when I print the report,
it multiplies by two again.

"Roald Oines" <r_*******@bresnan.net> wrote in message
news:Us********************@bresnan.com...
Richard Hollenbeck <ri****************@verizon.net> 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(Cancel As Integer)
: numOfA = 0
: numOfB = 0
: numOfC = 0
: numOfD = 0
: numOfF = 0
: End Sub
:
: Private Sub GroupHeader1_Format(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?
--

Nov 13 '05 #3
Richard Hollenbeck <ri****************@verizon.net> wrote:
: Yes it's multiplying it by 2 initially, but then, when I print the
: report, it multiplies by two again.
:
: "Roald Oines" <r_*******@bresnan.net> wrote in message
: news:Us********************@bresnan.com...
:: Richard Hollenbeck <ri****************@verizon.net> 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(Cancel As Integer)
::: numOfA = 0
::: numOfB = 0
::: numOfC = 0
::: numOfD = 0
::: numOfF = 0
::: End Sub
:::
::: Private Sub GroupHeader1_Format(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
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.

--
Nov 13 '05 #4
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_*******@bresnan.net> wrote in message
news:Us********************@bresnan.com...
Richard Hollenbeck <ri****************@verizon.net> 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(Cancel As Integer)
: numOfA = 0
: numOfB = 0
: numOfC = 0
: numOfD = 0
: numOfF = 0
: End Sub
:
: Private Sub GroupHeader1_Format(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?
--

Nov 13 '05 #5
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_*******@bresnan.net> wrote in message
news:XJ********************@bresnan.com...
Richard Hollenbeck <ri****************@verizon.net> wrote:
: Yes it's multiplying it by 2 initially, but then, when I print the
: report, it multiplies by two again.
:
: "Roald Oines" <r_*******@bresnan.net> wrote in message
: news:Us********************@bresnan.com...
:: Richard Hollenbeck <ri****************@verizon.net> 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(Cancel As Integer)
::: numOfA = 0
::: numOfB = 0
::: numOfC = 0
::: numOfD = 0
::: numOfF = 0
::: End Sub
:::
::: Private Sub GroupHeader1_Format(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
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.

--

Nov 13 '05 #6
"Roald Oines" <r_*******@bresnan.net> wrote in message
news:XJ********************@bresnan.com...
Richard Hollenbeck <ri****************@verizon.net> 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
might have an alternative suggestion.
Nov 13 '05 #7
Just use the format count var to check this:
Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

if FormatCount <> 1 then
exit sub
end if

--
Albert D. Kallal (Access MVP)
pl*****************@msn.com
Nov 13 '05 #8
Is there a compelling reason *not* to put the (BETWEEN a AND b) =
Nov 13 '05 #9
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!
Nov 13 '05 #10
"Richard Hollenbeck" <ri****************@verizon.net> wrote in
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:

Add some code to the On Retreat event of the GroupHeader1 object

If txtAvg >= 0.92 Then
numOfA = numOfA - 1
End If
etc.
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #11
Yep! I'm using the "page n of m" numbering. I don't really need it since
it's only a two page report. I'll eliminate it to see if that helps. But
that might not explain why it formats again at print-time, or if it can't be
avoided, how I can prevent my code from being run twice. I was hoping there
was another way to get counting code against each record rather than On
Format. After I remove the page numbering I'll know if I need to post more
information. Thanks for the great idea. If you hear from me again in this
thread it's because it didn't completely solve the problem. At that time,
I'll post the entire module behind the report.

Rich
"Brian" <bc**@IHATESPAMclara.co.uk> wrote in message
news:10***************@eunomia.uk.clara.net...
"Roald Oines" <r_*******@bresnan.net> wrote in message
news:XJ********************@bresnan.com...
Richard Hollenbeck <ri****************@verizon.net> 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
might have an alternative suggestion.

Nov 13 '05 #12
The report is already based on a complicated query. I'm trying to let the
report itself do some of the work. Part of the problem with adding this
computation into the already complicated query was that it was hassling me
with errors something like "such and such a field was not part of a valid
operation in this kind of an aggregate query bla bla bla..." Of course, I'm
paraphrasing here. Some of my other posts show the exact errors and code
causing the errors. I thought I'd leave good enough alone since I am
getting correct values for the grades of each student. I just need to count
how many students got an A, a B, etc. The query involved with calculating
hate to screw up a good thing by trying to squeeze this extra piece of data
from it. I don't know if that is a compelling reason not to include it in
the query, but I've tried it with bad results and I'm working on another
approach. Thanks, Pieter. You might be right, but I don't know how to get
it done that way.

R

"Pieter Linden" <pi********@hotmail.com> wrote in message
Is there a compelling reason *not* to put the (BETWEEN a AND b) =

Nov 13 '05 #13
Wow! That's an interesting approach. I'm not sure I fully understand. Do
you mean that I should create a saved query and do something with it? Or
should I create a DAO.RecordSet based on a strSQL string and do something
with that? The report already is based on a query. Would I keep the new
code in the Format event? Thanks very much for your comforting words about
the Format event being difficult to handle. I was beginning to think I just
wasn't very sharp. Maybe it's still true that I'm not too sharp. However,
I **AM** learning this stuff, however slowly.

this follow-up question?

Rich

"Marco Pagliero" <em****@web.de> wrote in message
news:41**********************@news.newsgroups.ws.. .
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!

Nov 13 '05 #14
Hi Rich. Well, you say your report is based upon a query. In this very
query you should put the fields I listed.

You should anyway save all queries, give them a name and use these names
in all reports. SQL expressions in a report are real adventure. I find.

If you open the query in "design view", every field in the query has a
column. Some empty columns should be also there, on the right hand side.
If you write in an still empty column exactly this:
AA: iif(txtAvg >= 0.92 , 1, 0)
your query should show this "virtual" field AA too. I hope txtAvg exists
and is a number.

If txtAvg is computed in the same query this will possibly not work.
Then you have to make a new query having all fields of the first more
the fields I listed.

SQL: select firstQuery.*, iif(txtAvg >= 0.92 , 1, 0) as AA, ... from
firstQuery

If you open this second query you should see all fields from the first
more AA, BB, CC, DD, and FF. In any row should AA, BB, CC, DD, FF be
Zero except the right one beeing One.

If this works, then you must base your report upon this new query. You
put in your report_footing five textfields named from A to F,
ControlSource set from
=sum(AA)
to
=sum(FF)

and this should show the sums you are looking for.

These "sum(xy)" you cannot put in the query, they must be in the report.

If this works, you should take away all code from the format event and
all fields related to this code from the report. My approach works
putting an One in every row I want count, a Zero in all other rows and
letting the report count them. It has nothing to do with the format
event.

doesn't work. Originally I was fighting with the format event trying
simply to write a count number in front of every report row. When I
discovered that a report not only is formatted several times, but that
every single formatting starts anew on NewPage I let this event alone
and sought for other ways to obtain the same effect.
The problem is, you don't know when an after-formatting begins, so you
cannot reset your counters. This is possibly an error in Access. Or
maybe not.

Anyway it is difficult to talk about queries and fields without the
right names, so if you need a better explanation tell me please: what is
the name of the query the report is based upon and how do you compute
the field txtAvg in this query. Maybe the SQL form of the query would
also help me.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #15
Thanks! Yeah, the problem is that the only time I can imagine that the
report generator sees an individual record is at the format event, isn't
that true? Unless I could some squeeze this extra piece of information from
this query. You see, the final score isn't the average YET. The average is
really a weighted average (activity weight and group weight) of the
different activities in different groups for one student in one course. The
final score for that student in that course will be the sum of these
weighted averages (i.e. the calculated true value of mid-term + the
calculated true value of Final Exam + the calculated true value of ThisEssay
+ the calculated true value of ThatEssay + ....). So I have a field in the
query that adds these values up and gives a final score. No problem there.

So if x activity is worth y percent of that group of activities and that
group of activities is worth z percent of the total semester grade, then
that activity's weighted average would by worth x * y * z. By adding them
up I will get the final grade for that student in that course. Now I know
how to compare the values to find out if it is an A, or a B, . . ., or an
F, etc., but what I want to do is count the number of students that are
earning an A, or a B, . . ., or an F, etc. That's where it gets hairy.
When I try to count a sum of (score * activityWeight * groupWeight) the
query FREAKS OUT--especially when I take it to the next level and try to
count how many students got that
count(sum(score*activityWeight*groupWeight))

Final output might look something like this:
A -- 4 Students
B -- 7 Students
C -- 12 Students
D -- 6 Sdudents
F -- 3 Students

Not really--I have a different format for that. I'm actually going to put
it into a bar graph. I'm just trying to communicate my real question. Once
I get these numbers, the rest is child's play.

I actually got it to count this in the Format event, but we've already
discusses problems with that. I tried using the retreat event to decrement
the results without success. Since it formats twice, I tried multiplying
each result by 0.5 but then it still formats again at print time. Maybe I
can do that same multiplication trick again in the Print event!!! It's
ugly, but if it works, who cares?

Rich

"Marco Pagliero" <em****@web.de> wrote in message
news:41**********************@news.newsgroups.ws.. .
Hi Rich. Well, you say your report is based upon a query. In this very
query you should put the fields I listed.

You should anyway save all queries, give them a name and use these names
in all reports. SQL expressions in a report are real adventure. I find.

If you open the query in "design view", every field in the query has a
column. Some empty columns should be also there, on the right hand side.
If you write in an still empty column exactly this:
AA: iif(txtAvg >= 0.92 , 1, 0)
your query should show this "virtual" field AA too. I hope txtAvg exists
and is a number.

If txtAvg is computed in the same query this will possibly not work.
Then you have to make a new query having all fields of the first more
the fields I listed.

SQL: select firstQuery.*, iif(txtAvg >= 0.92 , 1, 0) as AA, ... from
firstQuery

If you open this second query you should see all fields from the first
more AA, BB, CC, DD, and FF. In any row should AA, BB, CC, DD, FF be
Zero except the right one beeing One.

If this works, then you must base your report upon this new query. You
put in your report_footing five textfields named from A to F,
ControlSource set from
=sum(AA)
to
=sum(FF)

and this should show the sums you are looking for.

These "sum(xy)" you cannot put in the query, they must be in the report.

If this works, you should take away all code from the format event and
all fields related to this code from the report. My approach works
putting an One in every row I want count, a Zero in all other rows and
letting the report count them. It has nothing to do with the format
event.

doesn't work. Originally I was fighting with the format event trying
simply to write a count number in front of every report row. When I
discovered that a report not only is formatted several times, but that
every single formatting starts anew on NewPage I let this event alone
and sought for other ways to obtain the same effect.
The problem is, you don't know when an after-formatting begins, so you
cannot reset your counters. This is possibly an error in Access. Or
maybe not.

Anyway it is difficult to talk about queries and fields without the
right names, so if you need a better explanation tell me please: what is
the name of the query the report is based upon and how do you compute
the field txtAvg in this query. Maybe the SQL form of the query would
also help me.

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

Nov 13 '05 #16

### This discussion thread is closed

Replies have been disabled for this discussion.