Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display field results horizontally in Report (Access 2000)

    (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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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)?

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display field results horizontally in Report (Access 2000)

    Hans
    Here's the zip file.

    Thanks.
    Tom
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display field results horizontally in Report (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •