?
Solved

Gett rid of extra characters on a report field

Posted on 2014-11-10
5
Medium Priority
?
156 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 1000 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 49

Accepted Solution

by:
Dale Fye earned 1000 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 49

Expert Comment

by:Dale Fye
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

616 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