Solved

Gett rid of extra characters on a report field

Posted on 2014-11-10
5
148 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 39

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

815 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

8 Experts available now in Live!

Get 1:1 Help Now