One way to build the calculation is to use the List () function like so:
TABLE::City & ", " & TABLE::State & " " & TABLE::Postal_Code ;
When a value is empty ( For example, TABLE::Address_3 and TABLE::Address_4), the List () function "pushes" up the data underneath. (I'm sure there' s a better way to explain it, but it escapes me at the moment.).
We use a "mask" in a countries table, such as:
<<City>>, <<State>> <<Postal_Code>>
Then our full address is basically a substitute function, including getting rid of blank spaces.
Generally, I'd advise against address 1, 2, 3, etc. and recommend one address with potentially space for more than one line. Since there's no set rules as to what goes on each line, having multiple fields usually ends up with, for example, address2 sometimes being an apartment, sometimes a department, sometimes a c/o line, sometimes a PO box (which is actually a completely different address), etc., so searching the data becomes somewhere between difficult and impossible. My two cents.
I can't believe I forgot about the List function, thank you for bringing me back to this simpler solution.
In this case, it was simply:
Case ( Country = "United States" ; List ( Address 1 ; Address 2 ; Address 3 ; Address 4 ; City & ", " & State & " " & Postal Code ) ; List ( Address 1 ; Address 2 ; Address 3 ; Address 4 ; City ; State ; Postal Code ; County ; Country ) )
This allows for the calculation to provide the address in the US format when it's a US address and the extended blurb when it's not.