How to hide rows in a report?

[mattey]

Hi

My report for each group looks like this:

Header
(...)
[B]Detail[/B]
dana1 dana2 dana3 boolean(true)
dana1 dana2 dana3 boolean(true)
dana1 dana2 dana3 boolean(true)
dana1 dana2 dana3 boolean(true)
dana1 dana2 dana3 boolean(true)
dana1 dana2 dana3 boolean(true)
[B]Footer[/B]
sum (dana1)

[B]Header[/B]
(...)
[B]Detail[/B]
dana1 dana2 dana3 boolean(false)
dana1 dana2 dana3 boolean(false)
dana1 dana2 dana3 boolean(false)
dana1 dana2 dana3 boolean(false)
dana1 dana2 dana3 boolean(false)
dana1 dana2 dana3 boolean(false)
[B]Footer[/B]
sum (dana1)

I would like to show on my report all records that have last field true and hide all records with last field false, but the sum that calculates in Footer must stay and be count for all records.

Thanks for any help

 

[Magster]

In the Detail format event, you can check the value of me.YourFieldName and if is is false, then set Cancel = true and exit the Detail format subroutine.

The cancel will skip printing the row that has false, but I'm not sure about your counts you'll have to try and see.

 

[vbaInet]

I suppose you can count the recordset. Try that.

 

[mattey]

In the Detail format event, you can check the value of me.YourFieldName and if is is false, then set Cancel = true and exit the Detail format subroutine.

The cancel will skip printing the row that has false, but I'm not sure about your counts you'll have to try and see.

Should I make Macro, Expression or Code ?

 

[Magster]

Hi - I'm an oldtime programmer so I never learned much about macros, so I'll explain code - VBA. Although I think macros are a great learning tool if you take the time to convert them and understand what is happening...

When in design mode in your report, click on the Detail bar of your report, and then click on the property sheet; Click he Event tab and then click on On Format, and then on the ... In the Choose builder, click on Code Builder and then OK. Access will then create the Detail format subroutine shown below.

This is where you can insert code that will allow you to skip the printing of the row - or any part of the row that you don't want to show. If you want to check a false value, you could enter this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If not me.YourLastField then
Cancel = true
EndIf
End Sub

The Cancel will suspend that row's printing and continue to the next. I don't think this will impact your totals, but give it a try and if it does, write back and I'll check it out further.

 

[mattey]

Thanks! It worked
I prefer VBA too

Thanks again for your help

 

[Magster]

You're welcome - I'm glad that I could help.

 

[NZArchie]

Sorry to be bringing this back but how can I make this work for a whole group header section? Mine doesn't seem to be able to read the related field? My current code is:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If (Me.InsType <> "SWM") Then
        Cancel = True
    End If
End Sub

 

[Magster]

Hi - you have to click on the group header not the detail to make it work - but the idea is the same.

Open your report in design view, then click on the group header and open the property sheet and then click on the format event for that header (don't forget you may have more than one header row)

Let me know if you need help