Nested if statement using IsNull(v) for string fields

ChrisB_dup_72

I took a lot of data from and prepared in in 6 streams.  I Joined the data on [Building] but not all the steams had all the same buildings so some columns have nulls.  I wanted to create a new column that would take the building name from one of those 6 columns generated by the Join tool .  I tried a couple different conditional If statements but I wasn't successful.    Any help would be appreciated.  

The is a malformed statement at character 194

IF IsNull([Building]) THEN (IF IsNull([Input_#2_Building]) THEN (IF IsNull([Input_#3_Building]) THEN (IF IsNull([Input_#4_Building]) THEN (IF IsNull([Input_#5_Building]) THEN [Input_#6_Building])))) ENDIF

This one didn't work, left nulls

IF IsNull([Building]) THEN [Input_#2_Building] ELSEIF
IsNull([Input_#2_Building]) THEN [Input_#3_Building] ELSEIF
IsNull([Input_#3_Building]) THEN [Input_#4_Building] ELSEIF
IsNull([Input_#4_Building]) THEN [Input_#4_Building] ELSEIF
IsNull([Input_#5_Building]) THEN 0 ELSE 0 ENDIF

thank you.

AdamR_AYX

Personally I find the function version of IF (iif) easier in these kind of complex formulas. And I think doing a Not null (!isnull) also makes it easier to write and read.

iif(!isnull([Building])                , [Building],
iif(!isnull([Input_#2_Building]), [Input_#2_Building],
iif(!isnull([Input_#3_Building]), [Input_#3_Building],
iif(!isnull([Input_#4_Building]), [Input_#4_Building],
iif(!isnull([Input_#5_Building]), [Input_#5_Building],
iif(!isnull([Input_#6_Building]), [Input_#6_Building],
""))))))