Access 2013 - Multi-Line Concatenation of Strings

I have created a database in Access 2013 and am working on a report.  I have been attempting to figure out how to do multi-line concatenation but have run into some issues.  

My expression right now reads:
=[CovType1] & " - " +[Results1] & " - " +[Details1] & ".
" +[CovType2] & " - " +[Results2] & " - " +[Details2] & ".
" +[CovType3] & " - " +[Results3] & " - " +[Details3] & ".
" +[CovType4] & " - " +[Results4] & " - " +[Details4] & ".
" +[CovType5] & " - " +[Results5] & " - " +[Details5] & "."

I have put this into an expression under Control Source.

Users will fill out a form and will fill out up to 5 categories of results.  They will not always fill all categories (i.e., sometimes, a user may only have 2 categories; other times a user may have all 5; etc).  If there is no data in the category, then I want the data not to appear and to treat it as null.  I followed directions from Harfang's article (http://www.experts-exchange.com/articles/2040/Concatenation-in-Access-Reports-and-Forms.html) on a shortcut way to do that.

When I run the report, I get #Type! instead of the results I'm supposed to see for this field.

Any suggestions on how to make this work?  Thanks in advance!
Lisa HabaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
Check the name of the textbox where you placed this expression.  If I'm right, you probably modified an existing bound control so even though its control source is the expression you posted, the Name of the textbox is still the name of the field it was originally bound to.  So your expression is confused on where to get its data.  Once you change the name of the textbox, you should get what you want.

Ron
Lisa HabaAuthor Commented:
I just tried that and it didn't work.  I renamed the textbox to something found no where else in the database to avoid any confusion.  I also double-checked to be sure it wasn't bound in any way and it is not.  Its only source is the expression.

Ran it again and same error.
IrogSintaCommented:
If the textbox was originally named Results1 and you named it to LightOfTheWorld, check your expression and see if it got changed to LightOfTheWorld there as well.  If so, just change it back to Results1 and you should be okay.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Lisa HabaAuthor Commented:
I checked but the expression is the same.
Lisa HabaAuthor Commented:
I just deleted the textbox and drew a new unbound text box.  I added in the expression to the control source again.  I received the same error upon running the report.
IrogSintaCommented:
I copied your expression and used it in a report and it works fine for me.  I'm wondering if you have another textbox in your report that's causing the issue.  Get rid of all the controls in your report except this new unbound textbox and see if it works.  I normally select all the controls I want to temporarily remove and use CTRL-X to take them out.  If your expression works then paste the controls back in using CTRL-V and this time remove half of the controls.  Keep doing this till you find the offending control.

Or you could try a new blank report with just your expression in there.  Then if it works just add the other controls until it stops working.
Robert ShermanOwnerCommented:
FIRST, are you including that equals sign when you put that expression in to the controlsource property?  You shouldn't, it should just start with [CovType1]...   if that's not it..

I would suggest starting with a fresh unbound textbox control and then build up the controlsource a little at a time to find out what part is causing the error..

So, start with =[CovType1]

run the report, see if you have a value there..  

then add a bit more: =[CovType1] & " - " +[Results1] & " - " 

run it, see what you get... add the next field..

[CovType1] & " - " +[Results1] & " - " +[Details1]

And so on...  this will likely point you in the right direction within the first one to three tries..
Gustav BrockCIOCommented:
You miss the paranthesis:

=([CovType1] + " - " +[Results1] + " - " +[Details1] + ". ") &
([CovType2] + " - " +[Results2] + " - " +[Details2] + ". ") &
([CovType3] + " - " +[Results3] + " - " +[Details3] + ". ") &
([CovType4] + " - " +[Results4] + " - " +[Details4] + ". ") &
([CovType5] + " - " +[Results5] + " - " +[Details5] + ". ")

If this returns #Error, you are facing a duplicate/circular/missing naming issue as already explained.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lisa HabaAuthor Commented:
Thank you all for the suggestions - it was the parenthesis that made it work!  Appreciate the help!
Gustav BrockCIOCommented:
You are welcome!

/gustav
IrogSintaCommented:
Gustav, the parenthesis do not fix the issue at all.  That is, if the OP is trying to follow Harfang's article.  The whole idea was to include carriage returns in the concatenation.  The parenthesis gets rid of these carriage returns that are in there using CTRL-Enter.

Lisa, can you verify if this actually works if your Result1 and Details1 fields are null?  Do CovType2, Results2, and Details2 appear on the next line still or are they on the same line as CovType1?

Ron
IrogSintaCommented:
@Lisa, still waiting to hear back from you on whether the solution you used works as you requested.  Where each CovType begins in a new line even if other fields are null.

@Gustav, do you see what I mean about the carriage returns in Harfang's article?
Gustav BrockCIOCommented:
No, I just tried - from the controlsource shown - to imagine what Lisa wished to accomplish, and from the mix of & and + I could see she knew what she was doing except that the paranthesis to fulfill the goal were missing.

/gustav
IrogSintaCommented:
The reason there is a mix of ampersand's (&) and plus signs (+) has to do with a method as pointed out by Harfang that allows fields that are Null to not be included in the concatenation along with the characters beside them up the the next ampersand.  His article explains it this ways:
Technically, '+' propagates Null, in effect erasing the surrounding constant text up to the next '&' (and the '+' has operator precedence over '&', so it is always evaluated first).
So if you concatenate using the following expression:     = UCase([LastName]) & ', '+[FirstName] & ' '+[Initial]+'.' & ' ('+[Title]+')'  and your data has some null fields such as FirstName, Initial, or Title, you will not end up with extra commas or parenthesis.  For example, the following will format correctly no matter how complete or incomplete the data is:
DOE, John G. (Dr)
DOE, John
DOE (Dr)

In addition, the reason there are no parenthesis in Lisa's original post is that it was intended for Multi-Line concatenation where a New Line is inserted after the last apostrophe on each line by using CTRL-Enter.  Harfang explains all this in his article.

Ron
Gustav BrockCIOCommented:
Yes, as I wrote, the paranthesis were missing, but I didn't see any attempt to include line breaks.

/gustav
IrogSintaCommented:
Gustav, sorry but I'm not sure you follow.  The parenthesis have nothing to do with the problem which according to the title of this thread is Multi-Line Concatenation taken from that particular section of Harfang's article.   Let's say you have a series of records that had people's names and addresses, and some addresses had a 2nd address line for such instances as Apt#, while others fields were missing data.  If you just add your fields in this manner:
=[LastName] & ", " & [FirstName] & "(" & [Title] & ")"
=[Address1]
=[Address2]
=[City] & "," & [State] & "  " & [Zip]

Open in new window

Some of your labels will look bad because of missing fields such as the following that's missing the Title, Address2, and City:
Doe, John ()
123 Sesame St.

, NY  54321

Open in new window

However, if I expressed it this way along with carriage returns (CTRL-Enter) at the end of each line:
=[LastName] & ", " + [FirstName] & "(" + [Title] + ")" & "
" + [Address1] & "
" + [Address2] & "
" + [City] + "," & [State] + "  " & [Zip]

Open in new window

The result will concatenate properly:
Doe, John
123 Sesame St.
NY  54321

Open in new window


Ron
Gustav BrockCIOCommented:
I'm not sure about the expected output, but if multiple lines and using that method, it should just read:

=[CovType1] & " - " +[Results1] & " - " +[Details1] + "." & "
 " +[CovType2] & " - " +[Results2] & " - " +[Details2] + "." & "
 " +[CovType3] & " - " +[Results3] & " - " +[Details3] + "." & "
 " +[CovType4] & " - " +[Results4] & " - " +[Details4] + "." & "
 " +[CovType5] & " - " +[Results5] & " - " +[Details5] + "."

/gustav
IrogSintaCommented:
Actually the way Lisa had it was fine.  It worked when I copied and tested it on a report as I mentioned to her in one of my earlier comments.  However, now I believe the reason she couldn't get it working is that one of the fields is numeric.  If that's the case it needs to be converted to a string using Trim or Format as pointed out in the article.  So if CovType is a Number data type then she should use:

=Trim([CovType1]) & " - " +[Results1] & " - " +[Details1] & ".
 " +Trim([CovType2]) & " - " +[Results2] & " - " +[Details2] & ".
 " +Trim([CovType3]) & " - " +[Results3] & " - " +[Details3] & ".
 " +Trim([CovType4]) & " - " +[Results4] & " - " +[Details4] & ".
 " +Trim([CovType5]) & " - " +[Results5] & " - " +[Details5] & "."

Ron
Gustav BrockCIOCommented:
Oh. That explains.

/gustav
Lisa HabaAuthor Commented:
Hey all - sorry for delayed response - I didn't realize there was follow up on my post until today.  I added in parenthesis as Gustav had suggested and that made the error go away and it started working.  It has the carriage returns as well.  My expression is:

=([CovType1] & " - "+[Results1] & " - "+[Details1] & "

") & ([CovType2] & " - "+[Results2] & " - "+[Details2] & "

") & ([CovType3] & " - "+[Results3] & " - "+[Details3] & "

") & ([CovType4] & " - "+[Results4] & " - "+[Details4])

I am not an expert in Access and am still learning quite a bit - but this fixed whatever the problem was and it is now working properly.

Thanks for all the input

Lisa
IrogSintaCommented:
Lisa, as I said earlier, can you verify if it still works properly if your Result1 and Details1 fields are null?  Do CovType2, Results2, and Details2 appear on the next line still or are they on the same line as CovType1?

Ron
Lisa HabaAuthor Commented:
No they are on different lines as I was hoping it would be. it also recognizes the null feature
IrogSintaCommented:
Hmm, I just realized that you didn't use the parenthesis the way that Gustav showed you.  So you're really still following the correct syntax from the article.  Although I don't understand why in your situation, the parenthesis fixed the problem because where you put them shouldn't make a difference.  Can you indulge me one more time?  Remove the parenthesis and see if you get the #Type in your result again.  Just to satisfy my curiosity :-)

Ron
Lisa HabaAuthor Commented:
Sure thing - So now you have presented me with a new thing I can't entirely explain.  I did exactly what you were asking me to do (removed the parenthesis) when that solution was first presented to confirm that's what made it work.  Running it without them resulted in the error, and running it with the parenthesis successfully worked.  

Just now, I just tried that one more time, and it seems to be working with and without the parenthesis.  I have no idea why the same issue would have an error one day and not the next.  Sorry - I know this does nothing to help answer your curiosity!
IrogSintaCommented:
Well, that actually confirms what I was saying that the parenthesis shouldn't have made a difference right where you had them inserted.  As I said in my 3rd post, your exact syntax worked whey I tried it on a sample report I made.  My theory is that you had one field that was numeric which was causing #Type error.  However, in your latest test, you probably didn't have this numeric field in your table anymore.  Try changing one of the fields to a number and see if the #Type error returns.  If this was indeed the culprit, then the Trim function I posted here is what you really need.

http://www.experts-exchange.com/Database/MS_Access/Q_28682645.html#a40810297
Lisa HabaAuthor Commented:
Whats strange though is I have not changed any fields or made any of them numbers or not numbers.  I dont know how to change one field to a number without messing up the parameters that filter the results for the form.  Is that an easy thing or complex?
IrogSintaCommented:
I just meant to go into the table and temporarily replace the value of CovType1 in a single record to a number, then try viewing your report.  Be sure that the particular record you change is one that shows up in the report first though.

Ron
Lisa HabaAuthor Commented:
Its a drop down box... so I dont think I can do that. But I will try - like I said, I am not that experienced with all this so I could be wrong.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.