' Use for AddressType: basMultiLine is for a mailing-type address.
Public Enum baAddressType
    bamultiLine = 1
    baoneLine = 2
End Enum
'
Public Function BuildAddress(AddressName, Street, City, State, Zipcode, Country, _
    AddressType As baAddressType)
' This procedure builds an address; either a one-line address, or a mailing address.
' It passes back a Null if the arguments are all null or blank strings.
'
' BuildAddress() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_Handler

    Dim strSeparator As String
    Dim strAddress As String
    Dim strCityState As String

    ' Define a separator, based on the AddressType.
    Select Case AddressType
    Case bamultiLine
        strSeparator = vbCrLf
    Case baoneLine
        strSeparator = ", "
    End Select
    
    strAddress = ""
    
    ' AddressName
    If Nz(AddressName, "") <> "" Then
        strAddress = strAddress & AddressName & strSeparator
    End If
    ' Street
    If Nz(Street, "") <> "" Then
        strAddress = strAddress & Street & strSeparator
    End If
    
    strCityState = ""
    
    ' City
    If Nz(City, "") <> "" Then
        strCityState = strCityState & City & ", "
    End If
    ' State
    If Nz(State, "") <> "" Then
        strCityState = strCityState & State & " "
    End If
    ' ZipCode
    If Nz(Zipcode, "") <> "" Then
        If Right(strCityState, 2) = ", " Then
            strCityState = Left(strCityState, Len(strCityState) - 2) & " "
        End If
        strCityState = strCityState & Zipcode & ", "
    End If
    ' Country
    If Nz(Country, "") <> "" Then
        strCityState = strCityState & Country
    End If
    
    ' Add City, State ZipCode, Country to address.
    If Len(strCityState) > 0 Then
        strCityState = Trim(strCityState)
        If Right(strCityState, 1) = "," Then
            strCityState = Left(strCityState, Len(strCityState) - 1)
        End If
        strAddress = strAddress & strCityState
    Else
        ' If there is a separator at the end, leftover from above, then remove it.
        If Right(strAddress, Len(strSeparator)) = strSeparator Then
            strAddress = Left(strAddress, Len(strAddress) - Len(strSeparator))
        End If
    End If
    
    If Len(strAddress) > 0 Then
        BuildAddress = strAddress
    Else
        BuildAddress = Null
    End If
    
Exit_Proc:
    On Error Resume Next
    Exit Function
    
Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "BuildAddress()"
    BuildAddress = Null
    Resume Exit_Proc
End Function