Solved

Trim Envelope Labels

Posted on 2004-09-23
5
242 Views
Last Modified: 2012-05-05
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





0
Comment
Question by:DavidDF1913
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:justinbillig
ID: 12139229
try this

init = "" & init

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

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12139596
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
 

Author Comment

by:DavidDF1913
ID: 12144145
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 125 total points
ID: 12144287
You could:
=Trim([Salutation] & " " & [First] & IIf(Not IsNull([init])," " & [init],"") & [Last])

Although the line I gave you will also achieve the same effect.
0
 

Author Comment

by:DavidDF1913
ID: 12144978
This worked great!
Trim([Salutation] & " " & [First] & IIf(Not IsNull([init])," " & [init],"") & [Last])

Thanks,

David


0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now