Concatenate Address Consecutive Blanks (1 Viewer)

ECEK

Registered User.
Local time
Today, 11:31
Joined
Dec 19, 2012
Messages
717
I have the following in my query to concatenate my addresses.
Code:
Address: IIf(IsNull([Address_Line_1]), "", [Address_Line_1] & " " & Chr(13) & Chr(10)) & IIf(IsNull([Address_Line_2]), "", [Address_Line_2] & " "& Chr(13) & Chr(10)) & IIf(IsNull([Address_Line_3]), "", [Address_Line_3] & " " & Chr(13) & Chr(10)) & IIf(IsNull([Address_Line_4]), "", [Address_Line_4] & " " & Chr(13) & Chr(10)) & IIf(IsNull([TownCity]), "", [TownCity])

However, it doesn't give the correct results when there are two consecutive blanks.

Has anybody come across this before ?
I'm a bit puzzled as to how to get this to work.

As always your time and comments are very much appreciated.
 

Minty

AWF VIP
Local time
Today, 11:31
Joined
Jul 26, 2013
Messages
10,354
I use a function for 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

This puts carriage returns in - but could easily be changed to put in comma's instead.
 

ECEK

Registered User.
Local time
Today, 11:31
Joined
Dec 19, 2012
Messages
717
Minty.That is fantastic Works a treat.
Many thanks
 

Users who are viewing this thread

Top Bottom