Solved

Gett rid of extra characters on a report field

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

930 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

11 Experts available now in Live!

Get 1:1 Help Now