Hi Guys,
I have an Envelop Label Report, for some reason Address 1 or 2 are empty, how to hide or trim the line if it empty. e.g.

Fields Entry:
FullName 	:George
Company	:CompuNet2010
Address 1	:Al Khan St.
Address 2	:Empty
PO. Box	:19700
Country	:U.A.E.

Output should be:
George
CompuNet2010
Al Khan St.
19700
U.A.E.

Any help or tips is highly appreciated.


Cheers,
George

Try making the controls (I am assuming these are text boxes) for the 
Address info 0 Height and then make sure to change the Can Grow and Can 
Shrink property of the control to Yes. You can then select all the 
controls and do an align top and that will snap them together so you don't 
have white space when the address field is empty. 

BEWARE: It is a know problem that if you want to do this in a report you 
cannot have any controls to the left or right of the controls you want to 
grow and shrink (this means labels also). Otherwise it works quite well.

If you need controls left or right of the growing controls you have to 
enter the info through code using vbCRLF to insert a line feed. This is 
more complicated and I will not go into it unless you need me to.

Amy

> 
Hi Guys,
I have an Envelop Label Report, for some reason Address 1 or 2 are empty, 
how to hide or trim the line if it empty. e.g.

Fields Entry:
FullName 	:George
Company	:CompuNet2010
Address 1	:Al Khan St.
Address 2	:Empty
PO. Box	:19700
Country	:U.A.E.

Output should be:
George
CompuNet2010
Al Khan St.
19700
U.A.E.

Any help or tips is highly appreciated.


Cheers,
George

I wrote a VERY SHORT class module for this situation:

Option Explicit

    Private strAddress As String
'-----------------------------------------------------------
Public Sub Add(ByVal varIn As Variant)

    Dim strIn As String

    If IsNull(varIn) Then
        strIn = ""
    Else
        strIn = Trim$(CStr(varIn))
    End If

    If Len(strIn) > 0 Then
        strAddress = strAddress & strIn & vbNewLine
    End If

End Sub
'-------------------------------------------------------------
Property Get Out() As String

    If Len(strAddress) > 0 Then
        Out = Left$(strAddress, Len(strAddress) - 2) 'Remove Trailing New
Line
    End If

End Property


The reason for using a Variant as input and checking for Null is based on
the assumption that you will be deriving your data from a Table via a
recordset.

Hope this helps

Ian Ashton


-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Saturday, May 25, 2002 8:29 AM
To: Access
Subject: [access] Empty Field in Reports



Hi Guys,
I have an Envelop Label Report, for some reason Address 1 or 2 are empty,
how to hide or trim the line if it empty. e.g.

Fields Entry:
FullName 	:George
Company	:CompuNet2010
Address 1	:Al Khan St.
Address 2	:Empty
PO. Box	:19700
Country	:U.A.E.

Output should be:
George
CompuNet2010
Al Khan St.
19700
U.A.E.

Any help or tips is highly appreciated.


Cheers,
George