Access 2013 - Concatenated String Returning Number Instead of the Field Content

I have created a form that has a multi-line concatenated string.  My expression reads as follows:

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

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

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

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

The fields CovType1, CovType2, etc all are part of a drop down list where the user selects one of several options.  Each option in the drop down list is a word, and there are no numbers included.

When I run the form, the CovType field in each line returns a number.  The Results and Details fields all return the content that is supposed to be included.

Can anyone help me please get the CovType field to properly display the content it is supposed to?

Lisa HabaAsked:
Who is Participating?
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.

+ is generally not the correct concatenator to use.
Why have you mixed + and & ?
Replace the + and test

(+ can do some very neat things if null is in play
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You also will not get multiple lines like you want I think unless you include line breaks (vbCrLf).

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and just to be clear:

=[CovType1] & " - " & [Results1] & " - " & [Details1] & vbCrlf  & [CovType2] & " - " & [Results2] & " - " & [Details2] & vbCrlf  & [CovType3] & " - " & [Results3] & " - " & [Details3] & vbCrlf & [CovType4] & " - " & [Results4] & " - " & [Details4]

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
You could try using [CovType1].Column(0)

Gustav BrockCIOCommented:
It is related to this question - using "hidden" linefeeds:

Lots of silence.
So you are already using Harfang's neat trick

Each option in the drop down list is a word
Not necessarily.  It is VERY common practice to setup a combobox with something like
ID             Word
1               Alpha
2               Beta
3               Gamma
having a RowSource of "Select ID, Word, from Wherever"
The combo has a Bound Column of 1, and Column Count of 2, and Column Width of 0",2" and you will see

But the values are
I think that's your issue because you say
When I run the form, the CovType field in each line returns a number., so undoubtedly the bound columns are numbers, while you are seeing text.
Post the RowSources of the comboboxes for us to be sure but I think this may work.

=(CovType1.Column(1) & " - "+[Results1] & " - "+[Details1] & " 

 ") & (CovType2.Column(1) & " - "+[Results2] & " - "+[Details2] & " 

 ") & (CovType3.Column(1) & " - "+[Results3] & " - "+[Details3] & " 

 ") & (CovType4.Column(1) & " - "+[Results4] & " - "+[Details4])

Open in new window

Sounds like you have made the mistake of using a Lookup field in a table.  Lookups belong on forms.  They cause nothing but trouble with queries and code when you use them on tables.  It looks like they should be helpful since you see the lookup value rather than some undecipherable number.

The best solution is to remove the lookups from the table.  Add real relationships using the relationship diagram.  Then in any query where you need to return the text value, add a join to the lookup table and select the text value from there.
Lisa HabaAuthor Commented:
Sorry for delay in responding - personal emergency arose.  I appreciate the feedback so far.

Yes, as Gustav stated above, I am using Harfang's article to get the null effect he describes, and hence the combination of & and + signs.

Nick67, I tried adding in Column(1) as you suggested, but am running into an issue with it.  I use a form to specify the parameters that opens the correct record in the report.  When I add in the .Column(1), the parameters get messed up.  I generally understand what you are suggesting but am not 100% on the specifics.

I suspect you are correct that its pulling the bound column instead of the word that I want to appear.  So you understand the way I designed it, I created a table called "CovType" which contains 3 columns:

ID          Acronym           CovType
1           MSJ                    Motion for Summary Judgment
2           NJT                     Non-Jury Trial
3           CMC                   Case Management Conference
4           DS                      Docket Sounding

ID is the bound column and a relationship exists between this table and CovType1, CovType2, etc. in the table that all the report results are pulled from when my report is run.  

I don't know if that helps shed some light, but is there something there you suggest to get my report to give me the Acronym instead of the ID when the report is run?

Did you try Nick's suggestion to use the second column of the combo - cboSomeName.Column(1)?

Combos are a zero-based array so the second column is addressed as .column(1).  Usually it is the first column that is bound and the second column is what is displayed so you may need to adjust the column number to fit your RowSource definition.
When I add in the .Column(1), the parameters get messed up.
What does 'meesed up' mean in this context?
Are they not messed up already?  You had said
the CovType field in each line returns a number.
Did it in fact return the ID of each CovType, or did some math occur?

ID is the bound column and a relationship exists between this table and CovType1, CovType2, etc. in the table that all the report results are pulled from when my report is run.  
That leaves some murkiness.
Please post the .RowSource of at least one of these combo boxes.

Let's start simple.
We first need to get the column you wish out of your combo boxes.
Throw a testing textbox on the form
Set it's ControlSource to
Create an AfterUpdate event for CovType1
Have a line of code that requeries the test textbox
Play with CovType1 and the ordinal in
=(CovType1.Column(X) until you discover how to get the value you want in the test textbox
Lisa HabaAuthor Commented:
What I meant by "messed up" is that I have a form that has unbound fields to filter my results on the report to be a single record.  The user fills out the form specifying the parameters and then the report opens with the correct record.  When I added in the .Column(1), it gave me a popup box asking for a parameter for that field, which I understand is what happens when there is a field name that is not contained within the associated table.

The number is the same as the ID - I checked it and you are correct that this is where the number is coming from.  So at least we know the source of the problem.

You asked about the rowsource for the combo box -
For CovType1 it is SELECT [TypeCov].[ID], [TypeCov].[Acronym] FROM TypeCov ORDER BY [Acronym];
For CovType2 it is SELECT [TypeCov].[ID], [TypeCov].[Acronym] FROM TypeCov ORDER BY [Acronym];

Now you gave me a quick test to do on the form... if I get this working on the form, it will work on the report?  I may not be understanding, but the combo box on the form correctly shows the drop down options of Acronym.  Its the report that's not working.
Lisa HabaAuthor Commented:
I tried adding it onto the form as you suggested, and keep getting the error #Name?.  I also tried adding the same on a report and got the same error.
This is giving you #Name?

Dumb question: there is a combobox named CovType1 on the form, right?
#Name?  is generated if you use the name of a bound field as the name of the control but change the ControlSource from "MyField" to "=something"
Eureka @PatHartman!
For years I have wondered why folks longer in the tooth than me religiously renamed bound controls, given that the MS default for a bound control is the fieldname it's bound to.
Nobody has ever suggested a scenario that made that effort worthwhile--until now
I had never considered that someone might decouple the control from the field and have it go bang! as a result.

Thank you!  That's bugged me for years.

I too can't control myself when I find out little tidbits that resolve questions/problems I had for years:)  I still remember when I discovered that Access rewrites the RecordSource for reports to eliminate any column that is not actually bound to a control.

The other reason to do it is so you can differentiate between the bound field and the control.  In earlier versions of Access, intellisense didn't work quite right and if the control name wasn't different from the bound field, you didn't get some of the control properties/methods as options.  I haven't run into that in quite some time so Access may have gotten smarter.
I haven't run into that in quite some time so Access may have gotten smarter.
That was the usual reason given, but I started with A2003, and have never encountered it.
Lisa HabaAuthor Commented:
=CovType1.Column(1) - This is giving you #Name?
Yes, that's giving me that error.

PatHartman - I didn't quite understand your explanation of the error.  My field name and control source are the same... what did you mean by but change the ControlSource from "MyField" to "=something"

Dumb question: there is a combobox named CovType1 on the form, right?
Yes - the name and control source are both CovType1.
Lisa HabaAuthor Commented:
OK I have been playing around with this or got the sample box working on the form and it displays the result from as wanted.  The Name error has gone away.  I know you indicated getting this to work was the first step... what do you recommend should be next?
The control source is NOT bound to a column from the form's RecordSource.  It is not bound at all.  When a ControlSource starts with "=", that means that the value is "calculated".  In this case, "calculated" means that it is referencing a different control.

Change the Name property of the control to "txtCol1" and see if the error goes away.  Then change the name to whatever makes sense to you but is NOT the same as the name of any bound field.

Control Names and Bound fields end up in the same collection so if the control isn't bound to the recordsource field of the same name, then the Name property of the control must NOT duplicate the name of any bound column.

When you use the wizard to build a form or you drag a field from the field list, Access makes the Name property of the control, the same value as the bound field.  So, if I drag CustID from the field list to a form, the control is bound to the field named "CustID" and the Name property of the control is also "CustID".  This is no problem because Access works out when you reference Me.CustID what you are referencing.  However, if you later change the ControlSource to:
= SomeOtherField.Column(1), then you still have a control named CustID and you still have a field named CustID in the RecordSource but they are no longer referencing the same object so Access can't work out what you want to reference.
I know you indicated getting this to work was the first step... what do you recommend should be next?
You started with
=([CovType1] & " - "+[Results1] & " - "+[Details1] & " 
 ") & ([CovType2] & " - "+[Results2] & " - "+[Details2] & " 
 ") & ([CovType3] & " - "+[Results3] & " - "+[Details3] & " 
 ") & ([CovType4] & " - "+[Results4] & " - "+[Details4])

Open in new window

But that was giving you the bound column IDs of the comboboxes.
Through experimentation, you have now worked out the syntax of what you needed to replace the [CovTypeX] bits of the syntax with.

Now its time to use the syntax from your test textbox to get the right values for CovType1, CovType2, CovType3, and  CovType4 into your expression.
Lisa HabaAuthor Commented:
I have been playing with this for hours and have tried everything I can come up with and still not working.

I have attached my database.  When you open it, you'll have to sign in - username and password are both admin.  The relevant files are as follows:
TABLES: Coverage (contains Cov_Type1, Cov_Type2, etc fields)
               CovType (contains ID, Acronym that are causing the problems)
FORMS: Coverage_Results (this is the form users fill out to enter the data, including Cov_Type1, Cov_Type2, etc)
                Parameters_B&S Hrg (this is the form to set the parameters to open the report)
REPORT: B&S Hrg (this is the form that I'm having the problem with)

To open the report and see what I'm talking about, open the Form Parameters_B&S Hrg.  Fill in the following categories as the parameters:
Which Firm: Brock & Scott, PLLC
County Name; Pinellas - CW
Beginning Date of Range: 5/31/14
End Date of Range: 5/31/14
When the report opens, scroll down to where it says RESULTS/COMMENTS/... (a lot more text).  The portion underneath will read "4- GRANTED - text text text, etc." and then some more content after that.  Those numbers 4 and 3 are the ID that is showing up instead of the Acronym.  It should be showing the Acronym instead of the ID but is not.

I tried to put the CovType1.Column(1) on the report and the parameter form returned an error.  It treated it like it was an unknown field on the form.  I tried ignoring that and going to the report without specifying that paratmeter but gave me the #Name? error.  I tried changing it on the form itself in the combo box and then it stopped letting me enter the data into the entry.  I tried changing the relationships and that didn't accomplish anything.  

I am guessing maybe I am not applying the .column(1) part to the correct place in Access.  I appreciate the help!
Gustav BrockCIOCommented:
The short answer is: Convert these lookup fields to normal fields holding a foreign key to tables with your lookup values: "GRANTED","DENIED","GRANTED/DENIED IN PART" etc.

Yes, it will require a little right now but it will save you lots and lots of trouble. Just consider how much time you have spent on this single question. Further, given your knowledge of Access, it wouldn't be a question of "how", rather just doing it.

Many (most) of the "smart" features like lookup fields added in recent versions are aimed at super users, not developers. For these they represent mostly dead ends.

And should I mention an upgrade to SQL Server backend? Your application seems targeted at law firms, and these are used to strict security which you just cannot offer with an Access backend no matter what. At that point you will face a forced modification of the lookup fields.

I told you 9 days ago on June 6th that the problem was caused by using table level lookups.  You have to get rid of them.  They cause nothing but trouble once you attempt to use queries or code.

Also, you need to do some reading on normalization.  You have a number of repeating groups and those are also going to come back to bite you not to mention causing extra work in queries/forms/reports.  it may seem like more trouble initially to normalize - but the user said I'd only ever have 4!!!  And I have a bridge for sale.

And finally, don't use input masks for dates.  They don't ensure validity and they prevent the use of shortcuts and the date picker.  As long as Access knows that a control is supposed to hold a date field, Access will not allow an invalid date to be entered.
I cannot deal with an .accdb, so most help I can offer is nearing an end, but the initial question was
I have created a form that has a multi-line concatenated string.
Now you have
When the report opens, ...
I tried to put the CovType1.Column(1) on the report

That's just a completely different monster, and we've spent time trying to get a control on a form working.
Comboboxes don't EVER belong on reports because the user isn't going to be using that functionality, and reports are read-only anyway.  You build a query for a rowsource that gets the job done without need for a combobox.

the problem was caused by using table level lookups.
if you have comboboxes in TABLES, those need to go, tout de suite as the French say.
They will cause you no end of grief and trouble.
Store in tables the real data, not any kind of prettied-up version of it.
Tables are for storage, not presentation.

Also, you need to do some reading on normalization.
Look here

There is absolutely no end to the self-created hell that is known as creating a UI for non-normalized data.
Just when you feel you've suffered every way possible, there will be new pain.
Get your data design sorted.
The rest quickly begins to take care of itself.

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
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.