Address Envelope with Trim

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

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)) &

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Roger Bell" wrote in message
news
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