Remove Blank Address Lines (1 Viewer)

ECEK · Jul 13, 2018

I'm struggling to concatenate the data where two or more lines are blank.

I have an unbound text box on my form with the following.

=[Address_Line_1]+Chr(13) & Chr(10)+[Address_Line_2]+Chr(13) & Chr(10)+[Address_Line_3]+Chr(13) & Chr(10)+[Address_Line_4]+Chr(13) & Chr(10)+[City_Town]+Chr(13) & Chr(10)+[County]+Chr(13) & Chr(10)+[Country]+Chr(13) & Chr(10)+[Postcode]

However if Address_Line_3 AND Address_Line_4 are both blank then I get the following result:

1 Any Street
AnyArea

Townsville
Countyshire
United Kingdom
123 ABC

as you can see there is a blank on line three.

This anomaly occurs when there are two or more consecutive blank address lines.

Can anybody suggest a solution?

Minty · Jul 13, 2018

You can use a function to do this ;

Code:

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

isladogs · Jul 13, 2018

Or you can check for empty fields using the Nz function

ECEK · Jul 13, 2018

Thanks you guys

moke123 · Jul 13, 2018

You may also be able to use the Law of Propagating nulls.

Code:

Public Function LOPN(strIN As Variant) As Variant

LOPN = (strIN + vbNewLine)

End Function

then wrap your string

Code:

LOPN([Address_Line_1]) & LOPN([Address_Line_2]) & LOPN([Address_Line_3]) & LOPN([Address_Line_4]) & LOPN([City_Town]) & LOPN([County])  & LOPN([Country]) & LOPN([Postcode])

Remove Blank Address Lines (1 Viewer)

ECEK

Registered User.

Minty

AWF VIP

isladogs

MVP / VIP

ECEK

Registered User.

moke123

AWF VIP

Similar threads

Users who are viewing this thread