Text manipulation in report

[john]

We use Access 2007. A report prints envelopes. The first line can have one or
two names. There is always the first, name1, and optionally the second,
name2. At present both are printed with some space inbetween. As a missing
name2 is blank nothing shows on the envelope.
We would like to print "name1 and name2" if there is a name2. This could be
with [name1] & " and " & [name2]. However the word 'and' would appear even
when there is no name2.
There seems no way in Expression Builder to make an if statement.
Anyone know a non-programming solution to this?
Thanks,
John

 

[Jack Cannon]

John,

Something like this should work.

=[name1] & IIf(Len(Nz([name2], ""))=0, , " and " & [name2])

Jack Cannon

 

[john]

Thanks, Jack. It worked after using =0," ", rather than =0, ,
John

John,

Something like this should work.

=[name1] & IIf(Len(Nz([name2], ""))=0, , " and " & [name2])

Jack Cannon

We use Access 2007. A report prints envelopes. The first line can have one or
two names. There is always the first, name1, and optionally the second,
name2. At present both are printed with some space inbetween. As a missing
name2 is blank nothing shows on the envelope.
We would like to print "name1 and name2" if there is a name2. This could be
with [name1] & " and " & [name2]. However the word 'and' would appear even
when there is no name2.
There seems no way in Expression Builder to make an if statement.
Anyone know a non-programming solution to this?
Thanks,
John