If you’ve ever created a report and set a control’s Can Shrink property to Yes and then ran the report only for the item to be displayed with no data present then this post is for you.

The entire issue lies not with the control, but with it’s label. By having a label, Access assumes that it should be displayed regardless of the associated text control’s value. Therefore, Control with label = always display.

So what’s the solution you may ask?

The solution is to use a Text box for your labels, as well as setting it up so its’ control source to be dependent on the associated control’s value.

So say we have a FirstName control (to display a person’s first name) and we want to have a label associated with it. First you delete the default label control, then create a new text box control (this will act as the label) and finally set it’s Control Source to

=IIF(ISNULL([FirstName]),Null,”First Name:”)

So if the field FirstName is Null, then the text box label will be Null as well. However, if FirstName has a value, then the text box label will display ‘First Name:’

Now, once this is done, you can set the Can Shrink property of both control to Yes, and presto, that line will shrink if no value is present.

Now if only Microsoft could realize that if someone sets a control to Can Shrink, it implies that it should hide the label, we wouldn’t need all these types of workarounds to basic layout design.  At least offer the option to hide labels as well.  Kind of makes sense! If I didn’t want the associate label to shrink I wouldn’t be applying a Shrink property?!  At least the workaround in this instance isn’t too painful, still a pain, but manageable.