Null Values

[Guest]

I have a report with a number of values sources 1, sources 2 etc upto 20.
Each is a text box so on the report I put
="(" & "" & [Source 1] & " " & [Source 2 & " etc...

However if one of the fields in empty I don't want a space between the values
right now it shows

1 2 3 5

4 being empty

I want it to show:
1 2 3 5
is this possible.


Thanks
Jaime

 

[Wayne Morgan]

There are two ways.

1) ="(" & [Source1] & IIf(IsNull([Source2]), "", " " & [Source2]) &
IIf(IsNull([Source3]), "", " " & [Source3]) & ...


2) If all the values are text values (numbers as text won't work either
unless they are mixed with letters so that they won't evaluate as a number)
there is a shorter way. It takes advantage of the fact that Null propagates
an equation combined with using + for concatenation instead of &.

="(" & [Source1] & " " + [Source2] & " " + [Source3] & ...