Hide fields in a report if the value is null

[WildlyHarry]

I have a report that has twenty pieces of data on it. The problem is that
all twenty of those peices are rarely used but they can be. I would like to
hide/remove those fields in the even that data in them is null. I have a set
conditional formulas to hide the labels when there is no data, but access
keeps holding the space for the fileds. I have tried can grow and can shrink
but they do not work. In general may reports are four pages long with the
last two pages blank. I would like to eliminate some of this paper. Any
suggestions?

 

[Jeff Boyce]

First, if your labels are attached to their textboxes, you don't need to
"hide" the labels.

Next, if your CanGrow/CanShrink doesn't shrink the controls (and hide their
labels), any control directly to the right (?or left) of your "null" control
will hold it open.

Finally, if you haven't told the report section (e.g., the Details section)
to Grow/Shrink also, it won't know. Check the property settings on the
section.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[WildlyHarry]

1. They are not attached
2. There is nothing to the left or right
3. The section is property can grow and can shrink have been enable.

Anything else you can think of?

 

[Pat Hartman \(MVP\)]

Another possibility is that you may need to eliminate the null records from
the report's recordSource. If it is entire records that you are hiding, add
criteria to the report's recordSource query to eliminate them.

 

[WildlyHarry]

Unfortunately not an option because of the source query I use to build the
report. I have had to use a one to many join that shows all records from one
table and only the matching records from other tables to ensure that all of
ther required data is mined.

 

[Pat Hartman \(MVP\)]

Is the problem then that you are using only a main report when you should be
using a main report with a subreport? That way you wouldn't need the inner
join to get all the parent data to show and the subreport would be empty
when there are no child records.

 

[Jeff Boyce]

Consider attaching the labels. That way, you don't have to handle them
separately.

So, are you saying that you have a set of data to report (?a query) that has
some but not all of the values as Null? Can you (re-)confirm that at the
query level?

By "null", do you mean you can't see it, or that you can "get" the record by
using Is Null as a selection criterion? If you weren't already aware, the
zero-length string (zls, "") appears to be null but isn't really.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/