Need Help with Hiding Records

[jackson via AccessMonster.com]

Hi,

Currrenlty I hide records off a report so their data is included in the
subtotals (rahter than filtering out from query). The code I use for the
report is:

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

Me.Section(acDetail).Visible = ([Posn Type] <> "Cash" And [Position] <> 0)

End Sub

This hides records that are Position Type "Cash" or has a zero holding. I
have a new field in the underlying query called Tag that can be Yes or No. I
want to also hide any "Yes" records so I thought I'd just add in [Tag] <>
"Yes" in to that expression above. However this does not work and these
records still stay visible. Any thoughts?

Thanks.

 

[Allen Browne]

You are mixing AND and OR operators, so you will need to use brackets to
tell Access how to interpret it. It is important to understand that:
(a AND b) OR c
is not the same thing as:
a AND (b OR c)
where a, b, and c each represent phrases such as:
[Posn Type] = "Cash".

Additionally, reports have a Tag property, so Access is likely to
misunderstand what you want. Put a text box on the report, and name it (say)
txtTag, so it is not ambiguous.

This kind of thing, though I'm not sure how you want the bracketing:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim bHide As Boolean
If (Me.[Posn Type] = "Cash" And Me.[Position] = 0) OR (Me.[txtTag] =
True) Then
bHide = True
End If
With Me.Section(acDetail)
If .Visible = bHide Then
.Visible = Not bHide
End If
End With
End Sub

BTW, hiding sections like that only works if the page is actually previewed
or printed. If you open the report in Preview, and then print just page 6,
you are likely to get different results than if you print the whole 6 pages.
For this reason (as well as performance), it is a much better idea to
exclude the records from the reports, and then use DSum() or something to
get the calculated totals if you want them incldued in records that were not
in the report. This also solves the problem if filters are applied to the
report (e.g. via the WhereCondition of OpenReport.)