Shrink information on a sub-report when no data is available

SteveL13 used Ask the Experts™
I have a sub-report on a report that looks like this: (confidential information hidden)

Sub-Report Example
The sub-report has a box around it as you can see.  But here is what I need to do.  If, for example, the detail has no Tax ID # or no Main Phone or if any of the fields have no information, hide the label and shrink the data fields vertically including the box around the sub-form.  I do have the fields and the detail section of the sub-report set to Can Grow = Yes and Can Shrink = Yes.

Can this be done?  If so, how?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you could try the can grow / can shrink properties of the fields in question, and make it small to start
and use conditional formatting to change the label text color to white where no text exists
Most Valuable Expert 2015
Distinguished Expert 2018

To shrink the box, you will have to loop through the labels and hide those with no value in the taxtbox, then move all the following lines /label and textbox) one line up.
After the loop, there will be no empty values, and you should be able to shrink the box.
Most Valuable Expert 2012
Top Expert 2013
A no code solution, if you have a manageable number of fields to display in the box/subreport is to use a UNION query to define two visible columns (Labels and data) and a hidden Sort Order column to define what order the data appears in (ie; Company, LastName, FirstName, etc).  

The UNION Query would look something like this (adjust as needed to work with your database).  The idea here is to make the 'Label' NULL if there is no corresponding data, so that the CanShrink property will work if the Label text is used as the control source for a textbox.:

SELECT  EmployeeID, iif (LastName IS NULL, NULL, "Last Name:" ) AS TheLabel, LastName AS TheData, 1 AS SortOrder FROM tblEmployeeDetails
SELECT  EmployeeID, iif(FirstName IS NULL, NULL, "First Name:") AS TheLabel, FirstName AS TheData, 2 AS SortOrder FROM tblEmployeeDetails
SELECT  EmployeeID, IIF(Address IS NULL, NULL, "Address1:") AS TheLabel, Address AS TheData, 3 AS SortOrder FROM tblEmployeeDetails
SELECT  EmployeeID, IIF(Address2 IS NULL, NULL, "Address2:")  AS TheLabel,  Address2 AS TheData, 4 AS SortOrder FROM tblEmployeeDetails
SELECT  EmployeeID,IIF(Email IS NULL, NULL, "Email:")  AS TheLabel, Email AS TheData, 5 AS SortOrder FROM tblEmployeeDetails
SELECT  EmployeeID,  IIF(Phone IS NULL, NULL, "Phone:") AS TheLabel, Phone AS TheData, 6 AS SortOrder FROM tblEmployeeDetails;

Open in new window

In your subreport, place two textboxes, and *no labels* in the Detail section.  Set the control source properties to the label text and the data column.  In Sorting and Grouping, group on employee ID and  sort on the SortOrder column:
Report Design
Set the Can Shrink property of the textboxes and also the detail section itself to Yes.

Finally, instead of using a rectangle/box, just use the border of the subreport control, which if this is set up as described, will grow and shrink with the number of lines displayed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial