435,412 Members | 2,835 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,412 IT Pros & Developers. It's quick & easy.

Seemingly simple operation producing Wildly Crazy results

 P: n/a 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 Replies

 P: n/a Richard Hollenbeck 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 : : 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. Nov 13 '05 #2

 P: n/a Yes it's multiplying it by 2 initially, but then, when I print the report, it multiplies by two again. "Roald Oines" wrote in message news:Us********************@bresnan.com... Richard Hollenbeck 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 : : 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. Nov 13 '05 #3

 P: n/a Richard Hollenbeck wrote: : Yes it's multiplying it by 2 initially, but then, when I print the : report, it multiplies by two again. : : "Roald Oines" wrote in message : news:Us********************@bresnan.com... :: Richard Hollenbeck 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 ::: ::: :: :: :: 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. Nov 13 '05 #4

 P: n/a 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" wrote in message news:Us********************@bresnan.com... Richard Hollenbeck 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 : : 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. Nov 13 '05 #5

 P: n/a 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" wrote in message news:XJ********************@bresnan.com... Richard Hollenbeck wrote: : Yes it's multiplying it by 2 initially, but then, when I print the : report, it multiplies by two again. : : "Roald Oines" wrote in message : news:Us********************@bresnan.com... :: Richard Hollenbeck 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 ::: ::: :: :: :: 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. Nov 13 '05 #6

 P: n/a "Roald Oines" wrote in message news:XJ********************@bresnan.com... Richard Hollenbeck 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. Nov 13 '05 #7

 P: n/a 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) Edmonton, Alberta Canada pl*****************@msn.com http://www.attcanada.net/~kallal.msn Nov 13 '05 #8

 P: n/a Is there a compelling reason *not* to put the (BETWEEN a AND b) = in a query and then base your report on that? Nov 13 '05 #9

 P: n/a 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

 P: n/a "Richard Hollenbeck" wrote in news:Z_Fad.3549\$j15.2084@trnddc07: 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 that undoes your additions. 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

 P: n/a 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" wrote in message news:10***************@eunomia.uk.clara.net... "Roald Oines" wrote in message news:XJ********************@bresnan.com... Richard Hollenbeck 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. Nov 13 '05 #12

 P: n/a 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 those grades is already pretty complicated (from my perspective). I would 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" wrote in message news:bf*************************@posting.google.co m... Is there a compelling reason *not* to put the (BETWEEN a AND b) = in a query and then base your report on that? Nov 13 '05 #13

 P: n/a 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. Thanks, Marco! I appreciate your reply. Would you please reply again to this follow-up question? Rich "Marco Pagliero" 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 