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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.