IIF and Concatenate

[BHScripter]

Hi I am trying to concatenate text fields which contain names. I want it to take the main person and then if that person has roommates to add those behind the main person like so:

Mike Brown, Jim Smith, Matt Jones

However, if the roommate fields are null I don't want to see anything behind the main person. I have the following formula and I can't figure out what I'm missing. When the roommates are null I'm getting:

Mike Brown,,,,,,

The following is my formula syntax

{ado.sname}+IIF(isnull({ado.sroom1}),"",", "+{ado.sroom1})
+IIF(isnull({ado.sroom2}),"",", "+{ado.sroom2})
+IIF(isnull({ado.sroom3}),"",", "+{ado.sroom3})
+IIF(isnull({ado.sroom4}),"",", "+{ado.sroom4})
+IIF(isnull({ado.sroom5}),"",", "+{ado.sroom5})
+IIF(isnull({ado.sroom6}),"",", "+{ado.sroom6})

Thanks in advance,
Brianna

 

[lbass]

I think your syntax should work, too, but it doesn't for whatever reason. You could try the following instead:

whileprintingrecords;
stringvar name;

name := name + {ado.sname} +", "+
(if isnull({ado.sroom1}) then "" else {ado.sroom1} +", ")+
(if isnull({ado.sroom2}) then "" else {ado.sroom2} +", ")+
(if isnull({ado.sroom3}) then "" else {ado.sroom3} +", ")+
(if isnull({ado.sroom4}) then "" else {ado.sroom4} +", ")+
(if isnull({ado.sroom5}) then "" else {ado.sroom5} +", ")+
(if isnull({ado.sroom6}) then "" else {ado.sroom6} +", ")

Then use the following formula for the display in the group (Name) footer for:

whileprintingrecords;
stringvar name;

left(name,len(name)-2);

-LB

 

[vidru]

Another possibility is that fields aren't NULL, but empty strings. Try LB's solution first, and if the results are the same, try this:

{ado.sname} +
IIF((isnull({ado.sroom1}) or trim({ado.sroom1}) = "" ), "", ", " + {ado.sroom1}) +
IIF((isnull({ado.sroom2}) or trim({ado.sroom2}) = "" ), "", ", " + {ado.sroom2}) +
IIF((isnull({ado.sroom3}) or trim({ado.sroom3}) = "" ), "", ", " + {ado.sroom3}) +
IIF((isnull({ado.sroom4}) or trim({ado.sroom4}) = "" ), "", ", " + {ado.sroom4}) +
IIF((isnull({ado.sroom5}) or trim({ado.sroom5}) = "" ), "", ", " + {ado.sroom5}) +
IIF((isnull({ado.sroom6}) or trim({ado.sroom6}) = "" ), "", ", " + {ado.sroom6})

-dave

 

[tektipdjango]

the iif function evaluates both sides (true and false and if null there a "side effect"
Always use if then else syntax in case of null

django
bug exterminator
tips'n tricks addict

 

[BHScripter]

Hi:

Vidru's solution worked great. Thanks so much to everyone who answered