Hide Blank Rows in a Report

[jabrony76]

Hi all,

I've got a report that has fields for 10 pieces of evidence for every category. Problem is, there isn't always 10 pieces of evidence. Is there anyway to hide the fields that are blank? I've tried using VBA on the "OnOpen" of the report to say:

if me.Evidence1 <> 0 then me.Evidence1.visible = True
else
me.Evidence1.visible = False
end if

and so on for Evidence2, Evidence 3, etc...

This yields a debug error upon opening the report. Is there an easier way to do this? Thanks!
Andy


Basically, here is the setup of the report:

Category Header
Category
-----------------------------------
Detail
Evidence1
Evidence2
Evidence3
Evidence4
Evidence5
Evidence6
Evidence7
Evidence8
Evidence9
Evidence10

------------------------------------

Footer

 

[lameid]

So if the value is zero you want the control to be blank&quot;
Control source:
= IIF(NZ(evidence1,0) = 0, Null, evidence1)

I'm wondering why your values are 0 and not null if there is no evidence. If you had nulls in your table instead of zero's, you wouldn't have to worry about this.

 

[CosmoKramer]

Try the Trim function. Set the control source for the evidence controls to something like this:

=Trim([Evidence1])

etc......

 

[lameid]

You could also hide the controls on the on format event of the section they are in using the code you tried in your on open event. But you would also have to make them visible if they had data.

Sorry.. Should have mentioned that first since you were going down that path but my first post is still valid.