Mailing address from Multple Fields

[blackcat]

I have several fields that contain customer address data,ie Customer Name, addr1,addr2,addr3,post code etc etc.

 

If any of the fields are empty lets say for example Addr3& County then I want to create a field that conatains the full postal address with no gaps.

 

eg Fields are

 

Customer name

Addr1

Addr2

Addr3

Town/City

County

Postcode

Counry

 

With missing items as above reads as follows:

Customer name

Addr1

Addr2

 

Town/City

 

Postcode

Counry

 

I want or remove the gaps. Is there an easy way of doing this?

 

Thanks in Advance

Blackcat

[Robert Schaub]

I tried it with a calc that looks like this

 

Main First Name & " " & Main Last Name & "¶" &

Main address 1 &"¶" & Main address 2 & "¶" &City & ", " & State & " " & Zip code & "¶" & Main telephone number

 

and got the same results

 

Then I took out some of the

"¶" in between the fields , which force a new line and it worked.

 

The only problem with that is if there is data in a field that is not separated by a "¶" , it will be on the same line.

[David Head]

You need to add some conditions testing if data exists in a field.

 

I am assuming all fields should be on separate lines if they exist except Town/City, County and Postcode which are all on the same line. I also assume that there will always be a customner name, addr1, town/city.

 

Try this:

 

Customer name & "¶" &

Addr1 & "¶" &

If(IsEmpty(Addr2), "", Addr2 & "¶") &

If(IsEmpty(Addr2), "", Addr3 & "¶") &

Town/City & " " & County & " " & Postcode &

If(IsEmpty(Country), "", "¶" & Country)

 

Have fun!

[blackcat]

David

 

Close to what I already had, but good try.

Firstly all fields should be on seperate lines & not a combination as you indicated with Town city/county/ postcode.

 

If any of the fields are empty then the next line value should contain data from the next field down that has data in it.

However, if for example the first 4 fields are empty & the next available field was Country then I end up with the counrty field showing correctly under address1 but I also end up showing the counrty again in the next line as well.

 

Confused? so am I.

 

Steve

[blackcat]

David

 

Please ignore previous reply, I have played about some more with your solution & got it work perfectly.

 

I actually ended up with the following:-

Customer Name & "¶" &

 

 

If(IsEmpty(Address1) ,"",Address1 & "¶") &

If(IsEmpty(Address2) ,"",Address2 & "¶") &

If(IsEmpty(Address3) ,"",Address3 & "¶") &

If(IsEmpty(Address4) ,"",Address4 & "¶") &

If(IsEmpty(City Town) ,"",City Town & "¶")&

If(IsEmpty(County) ,"",County& "¶")&

If(IsEmpty(Postal Code) ,"",Postal Code & "¶")&

If(IsEmpty(Country) ,"",Country& "¶")

 

I am now a little stumped as to why this works, silly I know but I don't understand what quotes are doing.

 

please enlighten me!

 

Thanks again

Steve

[David Head]

Just a small change to suggest:

Customer Name & "¶" &

If(IsEmpty(Address1) ,"",Address1 & "¶") &

If(IsEmpty(Address2) ,"",Address2 & "¶") &

If(IsEmpty(Address3) ,"",Address3 & "¶") &

If(IsEmpty(Address4) ,"",Address4 & "¶") &

If(IsEmpty(City Town) ,"",City Town & "¶")&

If(IsEmpty(County) ,"",County& "¶")&

If(IsEmpty(Postal Code) ,"",Postal Code & "¶")&

If(IsEmpty(Country) ,"",Country)

 

You don't need the last & "¶" in the Country line because there is no need to put a return after Country if it exists.

 

As for your confusion - the double quotes with nothing in between them return nothing. For example, the line:

If(IsEmpty(Address1) ,"",Address1 & "¶")

 

reads this way in English:

if the Address1 field is empty, then return nothing, otherwise return the Address1 and a return character to start a new line.

 

So you see that you only start a new line when some data exists to precede it. Smart eh?

 

I think you have it.

[blackcat]

David

 

thanks for that, I understand!

 

As for the carrage return its a typo so I have deleted it.

 

I have made another slight ammendment to the final calc, If country is United states then the format is changed slighlty so that both count & Zipcode are on the same line.

 

If other countries have other formats these can be accomodated also.

 

Again thanks for your help on this.

 

Steve