Concatenate fields for address without blanks

[DaleG]

I am sure this is a very easy thing, but I can't figure it out. I am not good at this, just trying to help with a temporary solution. Anyway, I have a contract form that is to be used internationally. Since addresses vary, there is a field for everything:

[customername]
[CustomerStreet1]
[customerStreet2]
[cityarea]
[city_town_village]
[postalcode]
[country]

I created a concatenated field in a text box to pull everything together as an address block for the contract document that will be created:

Private Sub txtFullAdd_GotFocus()

Me.txtFullAdd.Value = Me.CustomerName & vbCrLf & Me.CustomerStreet1 & vbCrLf & Me.CustomerStreet2 & vbCrLf & Me.CityArea & vbCrLf & Me.City_Town_Village & vbCrLf & Me.PostalCode & vbCrLf & Me.Country


End Sub




I set the tab stop location right after country, so when its tabbed to, it will populate from the previously entered info. And that works. Except -- it doesn't take in to consideration if a particular field is blank. So, my address ends up looking like:

Test Customer
1313 Mockingbird Lane


Walla Walla WA 99362
USA

Is there a way to write it so that if a field is blank, it doesn't include the space for it?

[Gicu]

Use (+ vbcrlf + [field]) as the plus will remove null fields.

[mike60smart]

Hi

In an Unbound Textbox use the following:-

=([Mailingname]& Chr(13) & Chr(10)) &
([Address1]+Chr(13)+Chr(10)) &
([Address2]+Chr(13)+Chr(10)) &
([Address1]+Chr(13)+Chr(10)) &
([Address2]+Chr(13)+Chr(10)) &
([Address3]+Chr(13)+Chr(10)) &
([Town]+Chr(13)+Chr(10)) &
([PostCode]+Chr(13)+Chr(10)) & [Country]

[DaleG]

Thanks! I ended up using a series of IIF statements. The problem I kept running in to was that any time any of the fields were blank, the concatenated statement left a space for it.

[moke123]

I think it's called the Law of Propagating Nulls.
There used to be a pretty good wiki on it at UA but cant find anything there anymore.

Basically as Gicu points out Anything + a null is null.
It's the difference between using & or +

if you had rs!FirstName & " " & rs!MiddleName & " " & rs!LastName and there was no middle name you would have extra spaces using the "&"
if you have (rs!FirstName + " ") & (rs!MiddleName + " ") & rs!LastName and there was no middle name (it is a null) there would only be one space between first and last name as a (null + " ") is null so it would not include the second space.
You enclosed each part in parenthesis so the expression is evaluated together as a pair.

In your case as mike points out it would be CustomerName & (vbnewline + AddressLine1) & (vbnewline + AddressLine2) & (vbnewline + AddressLine3) & ... etc.

[ssanfu]

I tested this - no extra spaces

=([customername] & Chr(13) & Chr(10)) & ([CustomerStreet1]+Chr(13)+Chr(10)) & ([CustomerStreet2]+Chr(13)+Chr(10)) & ([cityarea]+Chr(13)+Chr(10)) & ([city_town_village]+Chr(13)+Chr(10)) & ([postalcode]+Chr(13)+Chr(10)) & ([Country]+Chr(13)+Chr(10))