Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trim Envelope Labels

Posted on 2004-09-23
5
Medium Priority
?
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

609 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