Displaying multiple fields in a row 2

[gillianleec]

I have a record that has 39 fields of test results, I would like to display the ones that are not null in a row with commas.

I have thought of arrays or for next loops (the field name is "fieldname" with n=1 to 39 but am not sure of the correct code. Please help!!


Gillian

 

[dhookom]

Try:
=FieldName1 + ", " & FieldName2 + ", " & FieldName3 + ", "...

Duane
MS Access MVP

 

[gillianleec]

This is a good idea but the two problems are: 1. There are extra commas when there is no data in a field and 2. there is a limit on the number of characters in a text field and if I include all 40 fields, I surpass that limit

Any ideas??

Gillian

 

[dhookom]

Concatenate them together in your query. BTW: are you sure there will be extra commas? Did you try this as I suggested with a combination of "+" and "&"? In my expression, if FieldName1 is Null then the following comma will not show.


Duane
MS Access MVP

 

[gillianleec]

AAAHHHH, some of the fields that I thought were null were just empty. This works great. Thanks. Now, I have another problem with 99 fields which is too many characters even for a query. I have written this function to handle it but do not know how to insert a fieldname.

Function combinetests()
Dim mypara As String 'this is the list of tests
Dim myfield As String 'this is the control name
Dim counter
dim RS as recordset

counter = 1
set RS=qry.recordset
Do While counter <= 99

myfield = &quot;test_id&quot; & counter
mypara = mypara + myfield
counter = counter + 1
Loop

End Function

The name of the field that I am trying to pull is [test_id1] and so on up to 99. I don't know to tell it to pull the data from the fieldname that matches myfield and then return that data. Am I close??

Gillian

 

[dhookom]

I thought you had 39 fields but now have 99? This is somewhat un-normalized. Are you trying to pull up test results or test names?
If I were you, I would create a series of queries that groups as many &quot;tests&quot; together as possible. Then create queries from queries.
This type of kludgy workaround is typical of un-normalized table structures.

Duane
MS Access MVP

 

[gillianleec]

I agree this table is ridiculously unnormalized. Out of my control-I am only the data extractor. I have several incidents with records that have multiple fields with the same name and then an integer. One has 39, another 99 and so on. I am trying to create a function that will allow me to pull the data, do comparisons (etc) and then display in report. Since this will be an on-going endeavour, I would like to create some generic code which I can modify for the task. For example, my next hurdle is to take 99 fields in one table and compare them to a value in another table and display the ones that match.

Anything will be helpful.


Gillian

 

[dhookom]

Your original posting was a request to display the non-empty fields as comma separated values in a single text field on your report. Is this still the issue? Is there a reason for this type of display?

Another option without creating any recordsets would be to bind all the fields to invisible text boxes in the detail section. It helps to think of the report as a recordset.
Add a single, large unbound text box named txtAll.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intI As Integer
Dim strText As String

For intI = 1 To 99
If Me(&quot;FieldName&quot; & intI) & &quot;&quot; > &quot;&quot; Then
strText = strText & Me(&quot;FieldName&quot; & intI) & &quot;, &quot;
End If
Next
strText = Left(strText, Len(strText) - 2)
Me.txtAll = strText
End Sub


Duane
MS Access MVP

 

[gillianleec]

I get a &quot;Can't execute code in design mode&quot; error??

 

[dhookom]

I didn't expect you to run any code in design mode. The code is in the On Format event of the detail section of the report.

Duane
MS Access MVP

 

[gillianleec]

That is where I thought I was running it...? I am not sure what I am doing wrong.

Gillian

 

[dhookom]

Based on my Aug 14 post, what have you tried? What are your actual field names? What were you doing that you got the &quot;Can't execute...&quot; message?

Duane
MS Access MVP