Hide Commas in text string

[ajb_1976]

Hi, I have a text box on a form that displays an address on one line by merging the address fields in a text string -
[Address1] & ", " & [Address2] & ", " & [Address3] & ", " &[Postcode] -
This works fine when all elements contain data, however sometime one of the address fields might be blank. When this is the case is it possible to code this line so that the ", " does not show if there is no data?

 

[Minty]

You can use a function to achieve this - put these two into a module and save them.

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

Public Function ShrinkSingleLine(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) & ", "
        End If
    Next
    ShrinkSingleLine = strLine
End Function

One does a single line, the other puts a carriage return in.

 

[ajb_1976]

excellent, thanks for the help!

 

[missinglinq]

This hack has been around for a long time, using the fact that anything plus a Null is Null:

[Address1] & (", " + [Address2]) & (", " + [Address3]) & (", " + [Postcode])

If [Address2] is Null, then (", " + [Address2]) is Null, and the Comma doesn't show up in your string.

Linq ;0)>