Concatenate fields using multiple IIF statements 3

[ncalcaterra]

hi there... i'd greatly appreciate any help on this little annoyance of mine.

i have the following fields:
Address1
Address2
City
State
Zip

they may or may not have values in them, depending on the situation. however, i still would like to concatenate them into a "nice" flowing string with proper puncuation. i sense i'm close, but my brain is just shutting down at them moment. heres what i have so far:

Address: IIf(IsNull([Address1]),"",[Address1] & ", ") & IIf(IsNull([Address2]),"",[Address2] & ", ") & IIf(IsNull([City]),"",[City] & ", ") & IIf(IsNull([State]),"",[State] & " ") & IIf(IsNull([Zip]),"",[Zip])

what i'm ultimately looking for is this:

address1, address2, city, state zip (and if one of them is null, it will not show the comma that would normally follow the value). with the statement i have above, a comma is displayed after Address2 (even when the field is null).

hope this makes sense and i really appreciate any time someone could offer me on this. thanks!

 

[Eupher]

Hi, ncalcaterra,

Personally I don't like to use multiple nested IIf's - too confusing and hard to debug. I would create a function to do the concatenation:

Public Function ConcatenateMe(ParamArray aValues() As Variant) As String
Dim i As Integer
Dim strMyString As String

For i = 0 To UBound(aValues)
    If Not IsNull(aValues(i)) Then
        strMyString = strMyString & aValues(i) & ", "
    End If
Next i

If Not strMyString = vbNullString Then
    strMyString = Left(strMyString, Len(strMyString) - 2)
End If
ConcatenateMe = strMyString

End Function

If there will *never* be any blank values, you could use this instead:

Public Function ConcatenateMe(ParamArray aValues() As Variant) As String

ConcatenateMe = Join(aValues(), ", ")

End Function

HTH,

Ken S.

 

[sahmiele]

This should work for you. You had your & ", " for address2 dependent on whether Address1 had data in it, not if address2 did or did not. I adjusted all of the formula for you. Hope it helps!

Address: IIf(IsNull([Address1]),"",[Address1]) & IIf(IsNull([Address2]),"",", " & [Address2]) & IIf(IsNull([City]),"",", " & [City]) & IIf(IsNull([State]),"",", " & [State]) & IIf(IsNull([Zip]),""," " & [Zip])

 

[ncalcaterra]

thanks so much euper & sahmiele! both methods work great. i appreciate your quick response - they've made my life much easier!

enjoy

 

[PHV]

And what about simply this ?
Address: [Address1] & (", " + [Address2]) & (", " + [City]) & (", " + [State]) & (", " + [Zip])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

PHV, you've done it again... As usual, your solution is the best of the lot. Have a star!

Ken S.