Address Envelope with Trim

[Roger Bell]

The following is code I use in a Report to address an envelope:
=IIf(IsNull([Postal Address]),Trim([Title for PG] & " " & [LName]) & Chr(13)
& Chr(10) & Trim([Unit/Flat] & " " & [Unit/Flat no]) & Chr(13) & Chr(10) &
Trim([Street NUmber] & "" & [Street Prefix] & " " & [Street Name]) & Chr(13)
& Chr(10) & Trim([suburb] & " " & [state] & " " & [pcode]),Trim([Title for
PG] & " " & [LName]) & Chr(13) & Chr(10) & Trim([Postal Address]) & Chr(13) &
Chr(10) & Trim([Postal Suburb] & " " & [State] & " " & [Postal PCode]))
The problem is that when there is no Unit Number, a line space is left
before the Street Number etc.
Can anyone advise me how I can correct this? Many thanks

 

[Douglas J. Steele]

You can take advantage of the fact that + treats Null values differently
than & when used for concatenation.

Null + anything is Null, while Null & anything is anything.

Rather than

Trim([Unit/Flat] & " " & [Unit/Flat no]) & Chr(13) & Chr(10) &

try

(Trim([Unit/Flat] + " " + [Unit/Flat no]) + Chr(13) + Chr(10)) &