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.
toddpotterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.