Solved

Gett rid of extra characters on a report field

Posted on 2014-11-10
5
145 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
Comment Utility
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
Comment Utility
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
Comment Utility
=[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)
Comment Utility
@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
Comment Utility
Excellent advise, Dale.  I'll go to work on changing the design.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
WebBrowser Control - ms/ACCESS 9 31
Resize text 4 15
putting an icon in a form 13 20
Double clicking on a button causes an issue 8 11
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…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

17 Experts available now in Live!

Get 1:1 Help Now