Access, hide blank fields AND Captions

[totoro]

I see how the CAN SHRINK will hide a blank field, but is it possible to do
the following:

My table may have several hundred records. the table has 40 fields, many are
blank.
When I print my report it has more blank fields than not, it is a waste.

I want my report to only print populated fields, and condense them (or move
them together) so there is not a lot of wasted page space, only the data.

ANy input is appreciated.

 

[Allen Browne]

You can shink the labels of you change them into text boxes, and set their
ControlSource so they shrink when their "attached" text box is null.

This example shows how to shrink the label that was attached to the City
text box:

1. Right-click the label, and Change To | Text Box.

2. Set the ControlSource of the new text box to:
=IIf([City] Is Null, Null, "City:")

3. Set its Can Shrink property to Yes.

Well, that's how to work around the problem. The chances are that if you
have 40 fields in a table, and many can be blank, you have built something
that would be good as a spreasheet but is not the right relational database
design. If you have repeating columns (such as years or week numbers, or
series such as Type1 and Type2 and ...), these should be many *records* in a
related table rather than many fields in the one column. This concept (the
one-to-many relation) is the most important thing to understand when
designing your database. Here's an example:
http://allenbrowne.com/casu-23.html

 

[Duane Hookom]

The CAN SHRINK works with null values and text boxes. You can change label
controls to text boxes with control sources like:
="The Caption " + [TheFieldName]
If TheFieldName is null, the above expression will be null and can be
"shrunk".

If you have about 40 with the possibility of lots of nulls, I would
question the table structure. It doesn't sound normalized butI could be wrong.

 

[totoro]

thanks for this, I used it, but it was not what I expected. I am frustrated
because I have a report that is very, very similar and that works, I created
it a few months ago and decided to make another for a similar application.
That one behaves the way I want, but some fields of the new report do work
predictable and some do not, and I cannot see what differentiates them. but
at least I got something printed out today. but its a work in progress.

thanks again

 

[totoro]

thanks Allen, I didn't design the db per se. this is a text file sent to me
on a regular basis, and I just try to make it more readable for the end user,
by pulling it into a simple mdb file and then running a report to print it
while hiding blank fields. thanks.

You can shink the labels of you change them into text boxes, and set their
ControlSource so they shrink when their "attached" text box is null.

This example shows how to shrink the label that was attached to the City
text box:

1. Right-click the label, and Change To | Text Box.

2. Set the ControlSource of the new text box to:
=IIf([City] Is Null, Null, "City:")

3. Set its Can Shrink property to Yes.

Well, that's how to work around the problem. The chances are that if you
have 40 fields in a table, and many can be blank, you have built something
that would be good as a spreasheet but is not the right relational database
design. If you have repeating columns (such as years or week numbers, or
series such as Type1 and Type2 and ...), these should be many *records* in a
related table rather than many fields in the one column. This concept (the
one-to-many relation) is the most important thing to understand when
designing your database. Here's an example:
http://allenbrowne.com/casu-23.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I see how the CAN SHRINK will hide a blank field, but is it possible to do
the following:

My table may have several hundred records. the table has 40 fields, many
are
blank.
When I print my report it has more blank fields than not, it is a waste.

I want my report to only print populated fields, and condense them (or
move
them together) so there is not a lot of wasted page space, only the data.

ANy input is appreciated.