Suppress group footer

[will eichert]

What's the best way to suppress a group footer when there's only 1
record in the group?

I have a group footer that sums an item in the detail records. Since
there is often only one detail record for a group, I don't want the
report to be cluttered with unnecessary sums for 1 value.

Thanks,

Will

 

[Jeff Boyce]

Will

One approach might be to use an IIF() statement in the group footer OnFormat
event -- something that tests the Count() of the details, and makes the
control(s) not visible/visible.

Good luck

Jeff Boyce
<Access MVP>

 

[will eichert]

Yes, I had tried this method. It sort of works, although the line
spacing is a mess. It breaks for a group footer even if the fields in
that group header are suppressed. Any easy way to fix this?

It seems like a better way is to supress the group break altogether if
there's only one detail record in that group. I can imagine doing this
in the OnFormat event of the Group footer in VB code with the
PrintSection and/or MoveLayout properties, but I'm unsure how to
reference the count of records in a group from VB. In Access, you can
use Count(*), but this doesn't seem to work in VB.

Also I don't know how to set these properties from the OnFormat event
in Access, even using IIF.

Is there a way to do either of these things?

Thanks,

Will

 

[Jeff Boyce]

Will

My bad! Use IF() in VB (IIF() in a query).

Would using Count([MyDetailControlName]) get what you're looking for?

Jeff Boyce
<Access MVP>

 

[will eichert]

No, that didn't seem to work for me. Maybe I was doing it incorrectly,
but I couldn't figure out a way to get a count of the detail records
in a group from within VB.

What did work was to place an invisible text box (I called it
grouprecordcount, pretty clever name, eh?) in the group footer, whose
data source is "= Count(*)". Then in VB in the group footer's Format
event,

If grouprecordcount <= 1 Then
Me.PrintSection = False
Me.MoveLayout = False
Else
Me.PrintSection = True
Me.MoveLayout = True
End If

Will

 

[Alick [MSFT]]

Hi Will,

To a count of the detail records in a group from within VBA, we can use the
group format event, and detail format event; for each group, the number of
calling detail format event is the number of the records in the group, in
other words, detail format event fires for each record; therefore, we can
put a variable in detail format event and increate it by one each time, and
then use the variable in groupfooter format event.

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

numcount = numcount + 1

End Sub

Please feel free to reply to the threads if you have any concerns or
questions.


Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: will eichert <[email protected]>
| X-Tomcat-NG: microsoft.public.access.reports
|
| No, that didn't seem to work for me. Maybe I was doing it incorrectly,
| but I couldn't figure out a way to get a count of the detail records
| in a group from within VB.
|
| What did work was to place an invisible text box (I called it
| grouprecordcount, pretty clever name, eh?) in the group footer, whose
| data source is "= Count(*)". Then in VB in the group footer's Format
| event,
|
| If grouprecordcount <= 1 Then
| Me.PrintSection = False
| Me.MoveLayout = False
| Else
| Me.PrintSection = True
| Me.MoveLayout = True
| End If
|
| Will
|
| On Tue, 7 Oct 2003 03:47:54 -0700, "Jeff Boyce" <[email protected]>
| wrote:
|
| >Will
| >
| >My bad! Use IF() in VB (IIF() in a query).
| >
| >Would using Count([MyDetailControlName]) get what you're looking for?
| >
| >Jeff Boyce
| ><Access MVP>
|
|