Setting up a report that won't print empty fields

[jenni via AccessMonster.com]

Using Access 2002, I have a report with about 60 fields, and of those 60, I
would like to print just the fields that have data in them. From what I have
gathered online, I think I need to use code, but I am self-taught on Access
and have not needed to use VBA until now. Could someone help walk me through
this? I appreciate any help!

Thanks ~Jenni

 

[Allen Browne]

If you arrange your text boxes on the report so that they do not overlap
each other vertically, you can set the CanShrink property of the text boxes.
If there is nothing else on the row, they will shrink, and the space will be
freed up. Provided the CanShrink property of the (Detail?) section is Yes as
well, no vertical space will be taken up by the shrunk text boxes.

Post back if you need further details, e.g. if these boxes have a label
beside them that you also need to lose when the field is blank.

BTW, if you have 60 fields in your table, and most are blank, there's a
fairly good chance that a related table would be a better design for the
fields that are repeating.

 

[Jenni Grubb via AccessMonster.com]

Where is the CanShrink property? I've viewed the properties looking for this,
and I cannot find it anywhere... not even in the help index/search.

A little more about this report ... it has 60 fields, but different records
will have a different variety of fields with data in them. One record may
have data in 10 fields, but another record may have data in 5 of those same
10 fields, but 12 additional fields as well. This database is for quality
assurance in medical files, so when things are missing or incomplete, notes
are made for the applicable field. But notes will vary for every file. I
need to be able to pop out a report for each department's records detailing
just the fields that need attention (have data in them).

More help is appreciated.
Jenni

 

[Allen Browne]

Open your report in design view.
Right-click a text box, and choose Properties.
On the Format tab of the Properties box, CanShink is about 5 down from the
top.

Don't forget to check that the section's CanShrink is set as well.

 

[Jenni Grubb via AccessMonster.com]

I found the Can Shrink and made the updates to the section and text boxes,
however, the report looks exactly the same as it did before. Help!

 

[Allen Browne]

Place a text box on a line on its own, with no other labels or controls
beside it, and none overlapping from above or below. Does it shrink now?

 

[Jenni Grubb via AccessMonster.com]

Hmmm... It works now, but shouldn't the label be attached? How will I know
what control (field) the data goes to? Is there a way for the label and text
box to shrink when there is no data in the texbox, but be visible when there
is data?

We're getting close!

 

[Allen Browne]

Great: you have it working.

Now for the trick to get the label to show only when the field has data.

In report design view, right-click the label, and Change To | Textbox.
Set the ControlSource of the text box to:
=IIf([Field1] Is Null, Null, "Show this text")
Set CanShrink to Yes for this quazi-label also.
When Field1 has a value, "Show this text" appears.
When Field1 is null, the quazi-label is Null, and so it shrinks also.

Make sure the Top of the label is exactly the same as the Top of the text
box, and they are exactly the same height. That way they can shrink
together.

 

[Jenni Grubb via AccessMonster.com]

Getting close...

I turned the label into a text box and set can shrink to yes...
I took your formula and inserted my field name to get the following:

=IIf([Consumer Data Sheet] Is Null,Null,"Show this text")

but all I get is #name? on the report, and it shows even though there is no
data.

I followed your directions to a T on a few fields and had the same prob.

What did I do wrong?

 

[Allen Browne]

Check the Name property of this text box. It cannot be the same as the name
of any of the fields in the report.

An alternative expression would be:
=IIf(IsNull([Consumer Data Sheet]), Null, "Whatever text")

Presumably
Consumer Data Sheet
is the name of your text box (with exactly those 2 spaces).

 

[Jenni Grubb via AccessMonster.com]

Gosh, these expressions just aren't working. All I get is #name?.

I made sure that the Name property is not the same as any other fields, but
its still not working.

 

[Allen Browne]

Start from the simple, and build up.

Can you get this working:
=[Consumer Data Sheet]

If that works but the IIf() does not, I guess you could have a problem with
a missing reference:
http://allenbrowne.com/ser-38.html

 

[Jenni Grubb via AccessMonster.com]

I can't even get =[Consumer Data Sheet] to work. I looked at the link
regarding references, but the files I need were checked. So I don't have a
clue at this point.

 

[Allen Browne]

In that case, it seems that you do not have a control on your report named
Consumer Data Sheet

Perhaps it is named something else? Perhaps without the spaces?

 

[Jenni Grubb via AccessMonster.com]

Ok, after taking a break from this horrible report, I went back and redid
every step in a new report. It seems things are better, however the "show
this text" part of the formula in the quazi-label is not showing up, so when
I do have data that needs to appear, only the data appears and no label.

Thanks for being patient with me. I think we've almost got it now.

 

[Allen Browne]

Debugging can be fun. ;-)
(Well, it can be frustrating anyway.)

Here's another version you could try:
=IIf(Len(Nz([Field1], "")) = 0, Null, "Show this text")

 

[Jenni Grubb via AccessMonster.com]

Eureka! It works... Now I just have to do the rest of my 60 quazi labels.

I do have one more question though. Since I had to put each control on a
separate line, is there a way to set the report to print a record on one page
only, even though the report setup is two pages long? Because the fields
shrink, I don't need a record to report on two pages... one with data, the
other blank. Actually the records can print one right after the other on
page if the record data is short enough to share a page.

Is that possible?

 

[Allen Browne]

The default behavior is to print on the one page.

Perhaps you have set some sections Force New Page property?