Trim Envelope Labels

I am printing a large number of labels with the following format:

[salutation] [first] [init] [last] [suffix]     * suffix example: Jr.,Sr.III
[contact]
[address1]
[address2]
[city] [state] [zip]

The main problem is line 1. [salutation], [suffix] and  Middle name (init) may be a NULL value. I need the proper Trim() procedure.

E.G.  if NOT ISNULL (init) then [first] & " " & [init] & " " & [last] else [first] & " " & [last].   Any help will be appreciated.

David





DavidDF1913Asked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
You could:
=Trim([Salutation] & " " & [First] & IIf(Not IsNull([init])," " & [init],"") & [Last])

Although the line I gave you will also achieve the same effect.
0
 
justinbilligCommented:
try this

init = "" & init

that will cast it to a string so if the value is null it will be an empty string

0
 
shanesuebsahakarnCommented:
Something like this:

([Salutation]+" ") & ([First]+" ") & ([Init]+" ") & ([Last]+" ") & [Suffix]

The + operator will yield a null value if the field is null so the space will be ignored. For example, if Init is null, the above evaluates to:
([Salutation]+" ") & ([First]+" ") & Null & ([Last]+" ") & [Suffix]

To clarify that better:
"a"+Null=Null
"a" & Null="a"
0
 
DavidDF1913Author Commented:
This is a modification to my original question.  Is it possible to do something like this in a single line?

=Trim([Salutation] & " " &  [First] & if not isnull ([init]) then " " & [init] & " " & [Last])

David

0
 
DavidDF1913Author Commented:
This worked great!
Trim([Salutation] & " " & [First] & IIf(Not IsNull([init])," " & [init],"") & [Last])

Thanks,

David


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.