• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Dale FyeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
hnasrCommented:
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 FyeCommented:
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 FyeCommented:
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
 
hnasrCommented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now