UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Totals Doubling In Report Footer, Any Version    
 
   
Raas
post Jan 18 2020, 06:31 PM
Post#1



Posts: 648
Joined: 27-January 07
From: Northern Arizona


I have read everything I can find on this subject, but can't seem to get it right anyway!
In the Detail Section I use an IIF to check if a student's score is < 700. If so, I then display "failed" in the text box (Results). Otherwise "Passed". To the side of that text box I use the Detail_Format to set a backcolor of red if the text box "Results" is "failed", Green if "Passed". I then use an IF statement to total the failed results, and the passed results. Everything seems to work fine. I can watch the accumulators (Passed and Failed), and they are totaling correctly. However, when I step through the code, the Report Footer Format cycles through twice and doubles the results.
I've read that this will always be the case??? If so, how can I display the text box results that should be printed in the report?
Thanks
Go to the top of the page
 
June7
post Jan 18 2020, 06:37 PM
Post#2



Posts: 1,269
Joined: 25-January 16
From: The Great Land


Step through what code? Provide code or db for analysis.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Raas
post Jan 18 2020, 06:55 PM
Post#3



Posts: 648
Joined: 27-January 07
From: Northern Arizona


Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.Passed1 = Passed
Me.Failed1 = Failed
End Sub


That's the code in the report footer.
Dim Passed As Integer
Dim Failed As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Text17 = "Passed" Then
Me.PassFAil.BackColor = RGB(0, 255, 0)
Passed = Passed + 1
Else
Me.PassFAil.BackColor = RGB(255, 0, 0)
Failed = Failed + 1
End If

End Sub
That's the code in the Detail Format. That works just fine.

As I stated in the question. I just want to know why the totals double and how to stop it.

Go to the top of the page
 
June7
post Jan 18 2020, 07:57 PM
Post#4



Posts: 1,269
Joined: 25-January 16
From: The Great Land


Eliminate the code.

Use Conditional Formatting.

Use Sum(IIf()) or Count(IIf()) expression in textbox.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Raas
post Jan 18 2020, 08:00 PM
Post#5



Posts: 648
Joined: 27-January 07
From: Northern Arizona


Tried that previously. Doesn't work.
This post has been edited by Raas: Jan 18 2020, 08:01 PM
Go to the top of the page
 
June7
post Jan 18 2020, 08:05 PM
Post#6



Posts: 1,269
Joined: 25-January 16
From: The Great Land


What does "doesn't work" mean - error message, wrong result, nothing happens?

It should work.

If you want to provide db for analysis, follow instructions at bottom of my post.

You already know the 'why' this happens - cycles twice, now why that happens I have never figured out. My suggestion is only solution I am aware of.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Raas
post Jan 18 2020, 08:19 PM
Post#7



Posts: 648
Joined: 27-January 07
From: Northern Arizona


Using =sum(IIF(Results <700)) Still produces a double count. The results should be 13, it produces 26.
Go to the top of the page
 
June7
post Jan 18 2020, 08:26 PM
Post#8



Posts: 1,269
Joined: 25-January 16
From: The Great Land


Then something else is going on besides the repeated cycle. I have never had issue with aggregate functions in textboxes.

Results must be a field in RecordSource, not a textbox name.

Your expression is incomplete.

=Sum(IIf([Results]<700, [Results], Null))


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post Jan 18 2020, 09:07 PM
Post#9


UtterAccess VIP
Posts: 11,646
Joined: 10-February 04
From: South Charleston, WV


Is this supposed to be a count of students who failed? Then you would use Count instead of Sum.
It might be easier to add a column to the report's query and sum it.

--------------------
Robert Crouser
Go to the top of the page
 
Raas
post Jan 18 2020, 10:06 PM
Post#10



Posts: 648
Joined: 27-January 07
From: Northern Arizona


Well, June. Here's what the actual complete formula shows:

=Sum(IIf([Results]<700,[Results],Null))

The text box for the failed students now shows 6476. It's supposed to only be 12.

I'll try the query suggestion. See what happens.
Go to the top of the page
 
Raas
post Jan 18 2020, 10:07 PM
Post#11



Posts: 648
Joined: 27-January 07
From: Northern Arizona


Robert: I took your suggestion and used the count function instead of sum. I swear that I tried that before with wrong results. I restarted the database and did it again. Voila! It works.

Thank you.
Go to the top of the page
 
projecttoday
post Jan 18 2020, 10:30 PM
Post#12


UtterAccess VIP
Posts: 11,646
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Jan 19 2020, 06:32 AM
Post#13


UtterAccess Moderator
Posts: 12,407
Joined: 6-December 03
From: Telegraph Hill


Robert has given you the sensible solution, however just for the sake of covering all options, you can also achieve the same result with Sum().

The expression would be:
CODE
=Sum(IIf([Results]<700, 1, 0))


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th February 2020 - 10:39 PM