I have a clients table where each record has several address fields. Eg. address 1, address 2, city, state, post code, country.

I want to join these fields into a single multi-line address but because my clients are spread all over the world, not all of then use need all the separate address fields filled in.

If I use the join() function with a carriage return as the separator it works but ant empty fields show up as an empty line. The same happens if I use the long hand

'field 1' + " carriage return" = 'field 2' etc

I want a way to join the compleated fields but ignore any that are empty.

I'm sure I could do this with complicated nested if-else statements but I was hoping for a more efficient way of doing this.

The only other option I can think of would be to have a single multi-line address field and re-enter all the addresses manually.

Any help would be appreciated.

Michael