Report - If null then dont display and collapse

Experts, I have this report and I want to add a field to the Detail section but this field is typically null and when it is null I need to collapse this field in the Detail section as it is a field that is below the other fields.  If I dont collapse it then the Detail section height is too high and the report doesnt look right.   I am knowledgeable of inserting a sub report with only this field and if null then collapse the subreport but its been a long time since I have done something like this and decided to post a question to see if there is either a better way now or still need to make a subreport.

thank you
pdvsaProject financeAsked:
Who is Participating?

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

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

Dale FyeOwner, Developing Solutions LLCCommented:
set the control propertis of AllowShrink and AllowGrow to Yes.  As long as there are no other controls to the right or left of the control, it will grow and shrink to fit the text in the control.
0
PatHartmanCommented:
You also need to set the CanShrink and CanGrow property to Yes for the section or the section won't shrink.  If the control has a label, you'll need code to show/hide the label depending on whether or not the control is null.

So, in the Format section for the control.

If Me.SomeField & "" = "" Then
    Me.SomeFieldlbl.Visible = False
Else
    Me.SomeFieldlbl.Visible = True
End If
0
pdvsaProject financeAuthor Commented:
Nice!  I will try that when get back to computer.  I didn't know about the fields to the right or left part you mentioned.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pdvsaProject financeAuthor Commented:
hmmm the control doesnt shrink even after I set both the Can Grow and Can Shrink to Yes for the control and the Detail Section.   There are no other controls to the left or right either.  

What do you think I should try now?  
Property Sheet for Detail Section
0
Hamed NasrRetired IT ProfessionalCommented:
Another way:

Set height of field to small value: .001
Can Grow :Yes

With this size, you may set Visible property to true or false in detail format event, without leaving extra blank. The textbox label can be dealt with in the same way.
0
PatHartmanCommented:
Make sure that the controls do NOT overlap.  That will prevent shrink/grow from working.
0
Dale FyeOwner, Developing Solutions LLCCommented:
In the image you sent, you have the detail section selected; you need to select the textbox control and set the CanGrow and CanShrink of the textbox as well as the Detail section.
0
pdvsaProject financeAuthor Commented:
The shrink of the section doesnt seem to work at all even if there are no controls to the right or left.  To be honest I now remember I have never had success with shrinking controls on a report if they are null.  
 I set both the Can Grow and Can Shrink to Yes for the control and the Detail Section.   There are no other controls to the left or right either.  I separated the label and control though as you can see in my pic above I combined them and therefore it was never null.  

The label seems to always be visible even if txtPastDueComments is "".
I have the label property set to visible = yes.  Tried No as well.  

If I can just get the label to turn off then I would be OK.  
here is the code:  
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

        If Me.txtPastDueComments & "" = "" Then
            Me.lblPastDueComments.Visible = False
        Else
            Me.lblPastDueComments.Visible = True
        End If

End Sub
0
Dale FyeOwner, Developing Solutions LLCCommented:
Well, if you delete the label, and set the control source of the textbox to:

ControlSource: = "Past due comments:" + [Past due Comments]

Then if [Past due Comments] is NULL, then the entire control source should be NULL, which would allow the CanShrink to work properly (plus there will be no comment to the left to have to hide.  When you use the ampersand to concatenate two values and one of the values is NULL, then the result is the non-null value.  When you use the plus sign to concatenate values and one or the other of them is null, the result will always be NULL


Otherwise, try:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Dim bVisible as boolean

    'the following line will test for NULL or a zero length string
    bVisible = (Trim(me.txtPastDueComments & "") <> "")
    me.lblPastDueComments.Visible = bVisible
    me.txtPastDueComments.Visible = bVisible

End Sub 

Open in new window

0

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
Hamed NasrRetired IT ProfessionalCommented:
Try this demo:

Label entered as a text field.
All fields height set to 0.01

Display Report1 in Print Preview

Detail_Format event.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Len(Nz(Field1, "")) = 0 Then
        Field1.Visible = False
        Label1.Visible = False
    Else
        Field1.Visible = True
        Label1.Visible = True
    End If
    
    If Len(Nz(Field2, "")) = 0 Then
        Field2.Visible = False
        Label2.Visible = False
    Else
        Field2.Visible = True
        Label2.Visible = True
    End If

End Sub

Open in new window

canGrowHide.accdb
0
pdvsaProject financeAuthor Commented:
the + trick worked.  thank you!
0
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.