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

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?
Who is Participating?
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.
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
Gustav BrockCIOCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.