asked on

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

Dale Fye

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.

PatHartman

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

pdvsa

ASKER

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.

pdvsa

ASKER

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?

Hamed Nasr

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.

PatHartman

Make sure that the controls do NOT overlap. That will prevent shrink/grow from working.

Dale Fye

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.

pdvsa

ASKER

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

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

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

pdvsa

ASKER

the + trick worked. thank you!