Hiding Subtotals in Access 2007 Report

[kristyspdx]

Hello,



I have a report that looks something like this:

Reporting Area1 $5,000
Group 1 $5,000
Group 2 $5,000
Group 3 $3,000
Group 3 $2,000

where the Reporting Area1, Group 1 and Group 2 are subtotals. Not every Reporting Area in the report has the same number of groupings, so I have some Group1 or Group 2 Values that are null, but the subtotals still show up on those rows, so it looks something like this:

Reporting Area1$5,000
$5,000
$5,000
Group 3 $3,000
Group 3 $2,000

I need to hide those rows where the Group1 or Group 2 fields are blank so those subtotals don't show.

Ideas? I tried reading through some of the VBA script for doing this, but I don't know much about VBA so would need very detailed instructions.

Thanks!

[alansidman]

   If isNull (ReportControl) Then
   ReportControl.Visible = False
   End If

Try this in your report On Load Event.

[June7]

How can there be subtotals if there is no data to add up?

[kristyspdx]

Where is the On Load Event? I only see the following in the property sheet:
On Click
On Format
On Dbl Click
On Mouse Down
On Mouse Up
On Mouse Move
On Paint
On Print
On Retreat

[kristyspdx]

The subtotals are calculated for each group regardless of if they have a group title. So the subtotals for group 2 are the sums of all the group 3s under that category and the subtotals for group 1 are the sums of all the group 2s under that category, so if there is no "label" for group 2, it is still the sums of all the group 3s, just with no label. I need to hide the rows with no labels.

[June7]

That makes no sense to me. Do you want to provide project for analysis? The Load event is a property of form. Do you have the Detail section selected when looking at the property sheet? You could also try the code in the Detail section Format event.

[kristyspdx]

alansidsman,

I tried the following code on format:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (IsNull([Division Subgroup1])) Then
[Division Subgroup1].Visible = False
End If
End Sub

And got the error message "Method or data member not found."

[June7]

Try:
If IsNull(Me.SubgroupDivisionTextboxName) Then
Me.SubtotalsTextboxName.Visible = False
End If

Use your actual textbox names where appropriate.

Or can use an expression in Subtotal textbox control source:
=IIf(IsNull([SubgroupDivisionFieldName]),Null,[SubtotalsFieldName])

Give the textboxes a name different from the fieldnames, like tbxSubtotal.

[kristyspdx]

I couldn't get the .Visible = False to work, but I did finally get my rows hidden (on print preview/print only--they are still visible in the layout view of the report which is okay.) I had two groupings I needed to hide if they had null values, so the following code worked in the "on format" event for each header:

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.Group_Of_Division_Supergroup) Then
Cancel = True
End If
End Sub

Private Sub GroupHeader3_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.Group_Of_Division_Subgroup1) Then
Cancel = True
End If
End Sub

Thanks for the help.