Collapse empty fields/ suppress blank lines and labels

Posted by: plankd on 4 August 2017, 3:00 pm EST

  • Posted 4 August 2017, 3:00 pm EST

    Hi there

    I have what appears to be a common problem but still can't find a satisfactory answer in the posts I've read. The "last word" on this would be really helpful.

    Let's say I have a report that looks like this:

    LABEL 1
    Text text text etc. etc.

    LABEL 2
    Text text text etc. etc.

    LABEL 3
    Text text text etc. etc.

    If, for example, the field underneath the LABEL 2 label is empty, I want to remove LABEL 2 from the report and collapse the blank space, thus:

    LABEL 1
    Text text text etc. etc.

    LABEL 3
    Text text text etc. etc.

    I can collapse the field happily enough using CanShrink, but the label is the problem. If I set it's VISIBLE property to false, it disappears, but the blank space isn't collapsed, so I get this:

    LABEL 1
    Text text text etc. etc.

    [space where LABEL 2 still exists but is not visible] 

    LABEL 3
    Text text text etc. etc.

    If I set the HEIGHT property to 0, it still doesn't work.

    I know I can solve this by adjusting the TOP value of each control on the page, but this seems a clumsy way to achieve what must be a common requirement.

    Send help soon!



  • Replied 4 August 2017, 3:00 pm EST

    You can have TextBoxes instead of labels for the fields that
    could be null and set their CanShrink property to True. These should be bound
    to a calculated Fields that you add in DataInitialize:

    Private Sub ActiveReport_DataInitialize()
        Me.Fields.Add "FaxLabel"
    End Sub

    Set the value of these fields in FetchData:

    Private Sub ActiveReport_FetchData(EOF As Boolean)
    IsNull(Me.Fields("Fax").Value) Then
    Me.Fields("FaxLabel").Value = ""
    Me.Fields("FaxLabel").Value = "Fax"
        End If
    End Sub

    You need to set the Detail section’s CanShrink property to

    Another solution is to concatenate all the fields that are
    above each other and the text for their labels with vbCrLf into one string in
    FetchData. For each field you can have an If statement to check the value of the field and set a corresponding string variable either to an empty if it is null or to the concatenation of a labelString + vbCrLf + field value string. After checking all the field concatenate the string varirables and set the defined field's value to it. In the report you can have one textbox that has the
    CanShrink property set to true and bound to the defined field.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels