• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1252
  • Last Modified:

Hide rows in Access 2007 Report

I am trying to hide rows in an access 2007 database report when the field 'PayStatus' contains certain text, but still have the amounts calculated in a running sum.  The Detail Section of my Report has the following code in the 'On Format' event, however, it still shows all of the rows on the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not Me.PayStatus = "Paid" Then
Cancel = True
End If
End Sub

Thanks for the help in advance.
0
toddpotter
Asked:
toddpotter
  • 6
  • 6
1 Solution
 
Patrick MatthewsCommented:
toddpotter,

The best way to handle this is to exclude these rows in the query you are using as the source for your report.

SELECT <columns>
FROM <tables>
WHERE PayStatus <> "Paid"

Patrick
0
 
toddpotterAuthor Commented:
If I exclude them from the query then they definitely won't calculate in my totals so I can't do it that way.  I am using a running sum so I need the calculations to happen.
0
 
Patrick MatthewsCommented:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Me![PayStatus] <> Paid Then Me.Detail.Visible = False

End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Patrick MatthewsCommented:
Oops!


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

    If Me![PayStatus] <> "Paid" Then Me.Detail.Visible = False

End Sub
0
 
toddpotterAuthor Commented:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Hide if Status = paid
If Me.PayStatus = "Paid" Then Me.Detail.Visible = False
End Sub

Above is the code I have in the event, but the report still shows all "Paid" rows
0
 
Patrick MatthewsCommented:
Please post a sanitized copy of your database
0
 
toddpotterAuthor Commented:
The report we are working on is "Payroll_PrePosting_Report
Experts.zip
0
 
Patrick MatthewsCommented:
Please post an MDB version of the file

:)
0
 
toddpotterAuthor Commented:
Here you go.
Experts-mdb.zip
0
 
Patrick MatthewsCommented:
toddpotter,

One very simple way to do this is to use Conditional Formatting.  For example, in the report design, select each field in the detail section in turn, and use a Conditional Formatting expression of [PayStatus]="Paid", and set the format to use white for the font color (making the font and background the same color has the effect of making the text "invisible").

That said,

>>I am trying to hide rows in an access 2007 database report when the field 'PayStatus' contains certain
>>text, but still have the amounts calculated in a running sum.

Won't people get confused if the running sum includes values they can't see?  You may get people calling you, and asking why the figures in that column don't foot :)

Patrick
0
 
toddpotterAuthor Commented:
The reason for excluding certain items is to shorten the report.  The report is used for commission pay histories and the end result of what I am trying to accomplish goes beyond hiding just the "Paid" ones.  There will be date criteria hidden also, but I started with "Paid" and I couldn't get it to work so that's why the question is centered around "paid" status.  I thought about the conditional formatting, but won't it leave a blank line and thus not shorten the report length?

Once all is achieved through the design of the report there won't be any confusion on the running sum because they are for "Draw" balances and the sales reps know that only the last xx records are being shown, but they can come to the accounting dept and get more information if needed.
0
 
toddpotterAuthor Commented:
Ok, it appears to be an Access 2007 issue.  I figured out how to get around it by changing the default view of the report to "Print Preview" and now it works.  Apparently the format event does not fire if the report is not set to Print Preview.  Anyone know why this would be the case?
0
 
ee_autoCommented:
Question PAQ'd, 500 points refunded, and stored in the solution database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now