Consolidating address fields where some elements are blank

[petejigsaw]

I am revising an often used report for marketing purposes, but have an issue with the address fields.

Our database stores addresses based on the following fields, with:

{cusmast.cm_address_1}
{cusmast.cm_address_2}
{cusmast.cm_address_3}
{cusmast.cm_address_4}
{cusmast.cm_address_5}

When extracting data for mailing etc, I want to use formulas for each line of the address so as to consolidate the data, so that if any of the fields are blank, I can in its place display the NEXT populated address field, so as to avoid blank lines in full addresses.

For example, if the following is in the database:

{cusmast.cm_address_1} = 1 Smith Road
{cusmast.cm_address_2} =
{cusmast.cm_address_3} =
{cusmast.cm_address_4} = Anytown
{cusmast.cm_address_5} = AnyState

I can get the following output:

Address line 1 = 1 Smith Road
Address line 2 = Anytown
Address line 3 = AnyState
Address line 4 =
Address line 5 =

I have tried any number of If/ELSE structures, but can't get it to work for anything other than checking the next line if the field being checked ISNULL.

Can anyone help by suggesting a simple forumla that I can use for each address line so as to achieve the desired outcome?

Thanks in advance

 

[LaurieHamlin]

I'm guessing that {cusmast.cm_address_1} always has something in it?
Try this {cusmast.cm_address_1} &
If(Trim({cusmast.cm_address_2}) > " " Then " " & Trim({cusmast.cm_address_2}) Else "" &
If(Trim({cusmast.cm_address_3}) > " " Then " " & Trim({cusmast.cm_address_3}) Else "" &
If(Trim({cusmast.cm_address_4}) > " " Then " " & Trim({cusmast.cm_address_4}) Else "" &
If(Trim({cusmast.cm_address_5}) > " " Then " " & Trim({cusmast.cm_address_5}) Else ""

 

[pmax9999]

You will need to check for both empty strings and null values. Null values will cause the formula to fail, and empty strings (or those with only spaces) will cause blank lines to appear.

Try this formula (which also assumes line 1 will always contain data - if not you will also need to add a test for that field):

{cusmast.cm_address_1} +
(
    If      NOT (Isnull({cusmast.cm_address_2})) and
            Trim({cusmast.cm_address_2}) <> ''
    Then    CHR(10) + {cusmast.cm_address_2}
    Else    '';
)
+
(
    If      NOT (Isnull({cusmast.cm_address_3}))and
            Trim({cusmast.cm_address_3}) <> ''
    Then    CHR(10) + {cusmast.cm_address_3}
    Else    '';
)
+
(
    If      NOT (Isnull({cusmast.cm_address_4}))and
            Trim({cusmast.cm_address_4}) <> ''
    Then    CHR(10) + {cusmast.cm_address_4}
    Else    '';
)
+
(
    If      NOT (Isnull({cusmast.cm_address_5}))and
            Trim({cusmast.cm_address_5}) <> ''
    Then    CHR(10) + {cusmast.cm_address_5}
    Else    '';
)

Hope this helps.

Cheers
Pete