Display field results horizontally in Report (Access 2000)

[THWatson]

(Edited by HansV to add <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags - normally, multiple spaces are ignored by HTML.)

The report is based on a query which combines data from two tables (one-to-many join on UniqueID). The main table holds names and other information. The linked table holds children of the names in the main table. The number of children may vary from 0 to 6.

Under normal design, the report will turn out with the child names displaying vertically...
Smith, John and Joan
Claude
Stephen
Joyce

I would like the child names to display horizontally as follows...
Smith, John and Joan
Claude, Stephen, Joyce

I can get the child names to display horizontally by using a multi-column subreport, but the display is less than desirable because too much blank space is placed between the child names. If the columns are 1 1/2 " wide, then the name of the second child will be as much as 1" away from the name of the second child, and so on...<pre>Smith, Joan and Joan
Claude Stephen Joyce</pre>

Any suggestions would be appreciated. Thanks.
Tom

[HansV]

You can use the Concat function attached to <post#=324705>post 324705</post#> to concatenate the names of the children. You can do this in a query, or in the control source of a text box.

If you would like more assistance, post back with details of table and field names.

[THWatson]

Hans
Thanks for pointing me to the Concat function. When I attempt to use it in a text box in my report, I get a Concat prompt, and then an #Error message. I am calling it in a text box using =Concat("tblChildren","ChildName")

The qry from which this runs is called qryDirectory. It combines information from tblTrinity and tblChildren, joined through tblTrinity.UniqueID to tblChildren.MemberID. The field I want to concatenate is ChildName from tblChildren.

Here's the Concat function as I have typed it. Perhaps there is something wrong in it.

Function Concat(aRSet As String, aField As String, aSeparator As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strRes As String
If aSeparator = "" Then
aSeparator = ", "
End If
If strSQL <> "" Then
strSQL = " WHERE" & Mid$(strSQL, 5)
End If
strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "] & strSQL;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
While Not rst.EOF
strRes = (strRes + aSeparator) & rst(aField)
rst.MoveNext
Wend
Concat = strRes
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Function

Tom

[HansV]

You made some mistakes in copying the code:
- You omitted the Where condition, which you are going to need.
- You omitted Optional before some arguments.
- You misplaced some quotes in the line that sets strSQL.

You will need to impose a where-condition to concatenate only the names of the children of the current parents. If you don't do that, you'll get an enormous string with all children in the tblChildren table. Can you indicate how the parents are identified in tblChildren? Is there a ParentID field or something like that, and if so, what is its data type (text or number)?

[THWatson]

Sorry about my mistakes, Hans. I haven't done much with constructing Functions so, obviously, missed some stuff. I also didn't copy it; I just printed out the post and re-typed (another mistake). Additionally, I thought that Optional meant you didn't have to include it if you didn't want to.

Back to the drawing boards.

To answer your questions...
The parents are identified in tblChildren through a MemberID (number, long Integer), which is the same number as UniqueID (AutoNumber, long Integer, Primary Key) in tblTrinity.

Tom

[HansV]

Thanks for the clarification.

1. You can select the function below, then copy it to the clipboard by pressing Ctrl+C, and paste it into a standard module by pressing Ctrl+V.

Function Concat _
(aRSet As String, _
aField As String, _
aCondition As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strRes As String
If aCondition <> "" Then
strSQL = " AND (" & aCondition & ")"
End If
If strSQL <> "" Then
strSQL = " WHERE" & Mid$(strSQL, 5)
End If
strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "]" & strSQL
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
While Not rst.EOF
strRes = strRes & ", " & rst(aField)
rst.MoveNext
Wend
If strRes <> "" Then
strRes = Mid$(strRes, 3)
End If
Concat = strRes
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Function

2. I assume your report is based on tblTrinity. To get just the children for a certain member, set the control source of the text box on the report to

=Concat("tblChildren","ChildName", "MemberID = " & [UniqueID])

I hope this helps, don't hesitate to post back if you still have problems.

[THWatson]

Hans
Well, I still get the same results. In the Report, I get a Concat parameter prompt. If I click OK when that appears, the text box shows #Error.

If I try to call the Concat function from the query, I get an Undefined Function error.

As indicated in my reply a couple of posts back, the report is based on qryDirectory.

Tom

[HansV]

Tom,

Would it be possible to post a stripped down version of the database?
<UL><LI>Make a copy of the database and work with that.
<LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
<LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
<LI>Remove or modify data of a confidential nature.
<LI>Do a compact and repair (Tools/Database Utilities).
<LI>Make a zip file containing the database; it should be below 100KB.
<LI>Attach the zip file to a reply.[/list]That would enable Loungers to take a look at the problem directly.

[THWatson]

Hans
Here's the zip file.

Thanks.
Tom

[HansV]

The problem is that you gave the module the same name as the function: both are named Concat. This confuses Access. You can remedy this by renaming the module to basConcat or something else - as long as it's different from the name of the function.

Notes:
1) When you are using Concat, you should not include tblChildren in qryDirectory, it will cause duplicate records. Just click on tblChildren in the design of the query, and press Delete.
2) The first ChildName in tblChildren (Michael) actually consists of three lines, as you will see if you increase the row height of the table. This probably happened by inadvertently pressing Ctrl+Enter instead of Enter.

[THWatson]

Hans
That did it! Thanks a lot for persisting with this with me.

And I learned a few things to boot - about naming Modules, and also about leaving a table out of a Query if you are using a Module.

As for the triplicate "Michael" I had noticed that too, but couldn't figure out why it was there. I had changed it in the entry form, but the triplicates kept coming back. Now I removed it from the table and re-entered it there. That seems to have fixed it.

Anyway, thanks again, Hans. This is of great help.

All the best.

Tom

[HansV]

Tom,

<hr>about leaving a table out of a Query if you are using a Module<hr>

That is not a general rule. The idea is more that you should not include more data than you need. In this case, you are using a special function to display the names of the children, but in other situations, you might use a built-in function.

Anyway, I'm glad it works now.