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
Solved

Gett rid of extra characters on a report field

Posted on 2014-11-10
5
149 Views
Last Modified: 2014-11-10
I have this as the control source of a field on a report.  

=[txtTech1LastName] & ", " & [txtTech1FirstName] & "  --  " & [txtTech2LastName] & ", " & [txtTech2FirstName] & "  --  " & [txtTech3LastName] & ", " & [txtTech3FirstName] & "  --  " & [txtTech4LastName] & ", " & [txtTech4FirstName] & "  --  " & [txtTech5LastName] & ", " & [txtTech5FirstName]

Open in new window


But if for example,  [txtTech5LastName] and [txtTech5FirstName] have no data then I want to not have the "  --  " and the ", " appear at all.

There would always be a [txtTech1LastName] and [txtTech1FirstName] value but the 2's, 3's, 4's and 5's may not have values.

How can I change the code to get rid of the extra characters?

--Steve
0
Comment
Question by:SteveL13
5 Comments
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 250 total points
ID: 40432640
You can use + instead of & to return null when one of the operands of the concatenation is null.

so, try this, but be aware that if txtTech2LastName OR txtTech2FirstName is null, then the Tech2's name will not appear. Same for Tech3, Tech4, etc...

=[txtTech1LastName] & ", " & [txtTech1FirstName] & ("  --  " + [txtTech2LastName] + ", " + [txtTech2FirstName]) & ("  --  " + [txtTech3LastName] + ", " + [txtTech3FirstName]) & ("  --  " + [txtTech4LastName] + ", " + [txtTech4FirstName]) & ("  --  " + [txtTech5LastName] + ", " + [txtTech5FirstName])

Open in new window

0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40432645
You need to use the '+' operator to add those field to your string.

=[txtTech1LastName] & ", " & [txtTech1FirstName]
& ("  --  " + [txtTech2LastName] + ", " + [txtTech2FirstName])
& ("  --  " + [txtTech3LastName] + ", " + [txtTech3FirstName])
& ("  --  " + [txtTech4LastName] + ", " + [txtTech4FirstName])
& ("  --  " + [txtTech5LastName] + ", " + [txtTech5FirstName])

Using the plus sign to concatenate strings will return a NULL if the value on either side of the '+' is NULL, but when you use the '&', it will return the value that is there.

Example:

?"ABCD" & NULL
ABCD
?"ABCD" + NULL
NULL

By wrapping each of the individual name groupings in a ( ) and then using the + to concatenate all of the stuff within each set of ( ) you can avoid all of the extra "--" and "," characters.
0
 
LVL 40

Expert Comment

by:als315
ID: 40432666
=[txtTech1LastName] & ", " & [txtTech1FirstName] & IIF(len([txtTech2LastName]& "" ) = 0,"","  --  " & [txtTech2LastName] & ", " & [txtTech2FirstName] ) & IIF(len([txtTech3LastName]& "")  = 0,"","  --  " &[txtTech3LastName] & ", " & [txtTech3FirstName]) &  IIF(len([txtTech4LastName] & "") = 0,"", "  --  " &[txtTech4LastName] & ", " & [txtTech4FirstName]) &  IIF(len([txtTech5LastName] & "") = 0,"","  --  " & [txtTech5LastName] & ", " & [txtTech5FirstName])

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40432704
@Steve,

Any time you have field or control names with numbers in them, it is a good sign of a poorly designed database.  Instead of having fields in your table for multiple techs, you should use a subform based on a table that only contains only one TechFirstName and one TechLastName.  That table would also contain a foreign key that would allow you to related the techs to the record in your main table.

This will allow you to add as many (or few) techs to your list as you need to and will not give you redundant columns.  It also makes it easier to identify what projects your techs are working, since you don't have to check all 5 fields of a single record to determine whether a particular tech is working on that project (or whatever your main form represents).
0
 

Author Comment

by:SteveL13
ID: 40432747
Excellent advise, Dale.  I'll go to work on changing the design.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

860 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