Eliminate Address Lines

[RogerCH]

I have a report that I have created for invoicing my Customers. How does one go about tidying up the display of the address on the report should some customers use less address fields?

For example

[tbxCustomerName] contains Name
[tbxAddressLine1] contains Street/Road
[tbxAddressLine2] (Town)
[tbxAdrressLine3] (City)
[tbxPostCode] contains Post Code

If a Customer record does not utilise a particular "AddressLine" I would like the report to display the next populated address field thus removing any blank lines.

I have tried using =IIf(isNull(tbxAddressLine2),then..,else) but I did get a few circular reference errors and struggled a little when having to nest a few IIf statements and then repeating

Any help would be gratefully received.

 

[theDBguy]

Hi. My approach is to concatenate the complete address and simply use one textbox to display the whole thing. I also use the Can Grow property to make sure it displays everything.

 

[Gasman]

Found this via Google?https://www.techonthenet.com/access/reports/hide_lines.php

 

[Minty]

Adding to theDBguys suggestion I have a simple function that removes the blank lines from an array of fields passed to it;

Public Function CanShrinkLines(ParamArray arrLines())
    '
    ' Pass this function the lines to be combined
    ' For example: strAddress = CanShrinkLines(Name, Address1, Address2, City, St, Zip)
    '
    Dim x     As Integer, strLine As String
    For x = 0 To UBound(arrLines)
        If Not IsNull(arrLines(x)) And arrLines(x) <> "" Then
            strLine = strLine & arrLines(x) & vbCrLf
        End If
    Next
    CanShrinkLines = strLine
End Function

 

[moke123]

you can also use the Law of Propigating Nulls. anything plus a null is null.

(Name + vbnewline) & (address1 + vbnewline) & (address2 + vbnewline) & etc.

 

[RogerCH]

Thank you Gasman for providing a very quick simple solution to the problem.
Only slight downfall is that I will need to extend the height of my Page Header to avoid "overlapping" with the controls on the left of the page where I have my Address displayed.
Cheers

 

[Gasman]

Well the other solutions might be better in that case?
All would be quick to test to see what works best for you?