Visible Conditional Group Totals

[PaulaJ]

I have set up a report with group totals accumulating for a Yes/No field = "True(-1)" in the Group Header using code. I works perfectly as long as I have the Group Header visible. All of the group and grand totals are correct. The Yes/No field is actually on the Group header and the accumulating field is also to verify that it is accumulating correctly. I only want the Group Footer records displayed. When I make the Group Header Visible=NO (no other changes), all of the Group Totals for the field become Zero. Does the Group have to be Visible to make the report behave correctly? Here is the code.

Option Compare Database
Option Explicit
Public CountUndelivered As Integer
Public GroupCountUndelivered As Integer
Public TotalCountUndelivered As Integer

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

GroupCountUndelivered = 0
CountUndelivered = 0

End Sub

Public Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)

If Me![Undeliverable] = -1 Then CountUndelivered = CountUndelivered + 1
If Me![Undeliverable] = -1 Then GroupCountUndelivered = GroupCountUndelivered + 1
If Me![Undeliverable] = -1 Then TotalCountUndelivered = TotalCountUndelivered + 1

End Sub

Thanks for helping!

 

[PaulBricker]

I can't say I know whether the Group Header has to be visible but you can try this in the control source for a textbox in your Group Footer

=Sum(IIf(Me![Undeliverable = -1,1,0))

Summing 1's and 0' is the same as counting.

Paul

 

[PaulaJ]

Good Idea. That makes sense. However, when I put that in the text box in the footer, it doesn't recognize the Me! and asks for me to fill in the parameter. I even put the [Undeliverable] field on the footer and it did the same thing. I've double checked all of the spelling and tried [Reports]![ReportName]![Undeliverable] with no luck. This report does not have any parameters. Any ideas?

 

[PaulBricker]

Is [Undeliverable] a textbox in the Group Header? With reports, it can be something little that messes it up.

Try
Me.[Undeliverable]

using the . (dot). When you enter the dot, you should get a dropdown with the field names as well as properties listed. Look for Undeliverable and set it that way. It probably won't make a difference but you can try it. Let me know where the field is located and any other relevant info.

Paul

 

[PaulaJ]

The .dot didn't make any difference. I'm entering it in the Properties box, so didn't get any dropdown. [Undeliverable] is actually a Yes/No field from a Table being accessed through a Query to produce the report. I got it to work by removing the Me!. The footer line prints correctly now. However, how would I total that field. Here are the specifics:

Footer: textbox named [GroupCountUndelivered] with control source of =Sum(IIf([Undeliverable]=-1,1,0)). This prints correctly. Invisible textbox name [AccumUndelivered] with a control source of =Sum[GroupCountUndelivered]

Report Footer: textbox name [GrandCountUndelivered] with a control source of =[AccumUndelivered]

It still asks for [GrandCountUndelivered] as a parameter. If I put something in it, I get a data type mismatch error. If I leave it blank, the report runs but the Report Footer totals are blank. Confusing, eh?

Thanks for looking at this. I'm pretty much on my own here.

 

[PaulBricker]

In reports you can't calculate a calculated textbox.
In your Report Footer put
=Sum(IIf([Undeliverable]=-1,1,0))

This will give you a total for all your groups. I assume that [AccumUndelivered] isn't necessary if you can do the totals right in the Report Footer. Let me know if I'm wrong.

Sorry about the Me!. I should know you can't use Me in the Control Source for a textbox. Duh!

Paul

 

[PaulaJ]

That was the ticket. I removed the invisible field from the footer and added the Sum field like you said. Voila! I do most of my reports in Crystal Reports and Access sure makes you do a lot of extra work. Anyhow, thanks for sticking with me. It looks perfect. PAULA

 

[PaulBricker]

Access reports can be daunting. Glad you got it.

Paul