Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report data in rows not columns

    Is there any way of creating a report listing that shows the details in a 'normal - paragraph fashion' rather than a long list. I need to limit the depth of a sub-report which contains names associated to each company in the main report. One company has > 20 so i would like just a paragraph of names in this instance.

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Location
    Great Lakes State, Michigan, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report data in rows not columns

    I can think of two things. Create a subform and link it to your main form using the child field/Parent field Property of the subform. Size the subform on your main form to the largest size you need it to be. Set it's Can Grow property to NO. Next go to the subform's page layout to set up as many columns you need and which way you want the data to flow in the columns. When you run the main report the data in the subform will flow into the columns instead of expanding downward. You have to make sure you size the subform properly so that no data is cut off since the Can Grow property is set to NO.

    Another option involves coding. You could create an unbound text box and populate it by looping through a recordset in the GroupHeader format event, separating the data by commas. Group By the company and place the text box in the group header not the detail section. Set the Can Grow property to YES. Here is how I have done this using DAO.

    Dim dbs As Database, rst As Recordset, strSQl As String

    strSQl = "SELECT Employees.CompanyID, "
    strSQl = "Employees.EmployeeName, "
    strSQl = strSQl & "FROM Employees "
    strSQl = strSQl & "WHERE (((Employees.CompanyID)=" & Me.CompanyID & "));"

    Set dbs = CurrenDb
    Set rst = dbs.OpenRecordset(strSQl)

    With rst
    'Check if there are any records. If not exit.
    If .EOF = True And .BOF = True Then
    Exit Sub
    End If

    Do Until .EOF = True
    'If there is no value in the text box add the first name
    If Len(Me.txtNames & "") = 0 Then
    Me.txtNames = !EmployeeName

    Else
    'Add the next names
    Me.txtNames = Me.txtNames & ", " & !EmployeeName
    End If
    'Move to the next record
    .MoveNext
    Loop
    End With

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

    [img]/w3timages/icons/yikes.gif[/img]

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Great Lakes State, Michigan, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report data in rows not columns

    SORRY! My sql was wrong! Should be:
    strSQl = "SELECT Employees.CompanyID, "
    strSQl = strSQL & "Employees.EmployeeName "
    strSQl = strSQl & "FROM Employees "
    strSQl = strSQl & "WHERE (((Employees.CompanyID)=" & Me.CompanyID & "));"
    [img]/w3timages/icons/laugh.gif[/img]

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report data in rows not columns

    Here's a code routine that will work in either 97 or 2000 and will return a delimited list of values from the specified table or query field based on the where string you pass it. You could use this in the underlying query. Your field in the query grid might look like this:

    Companies: BuildValuesList("tblCompanies","CompanyName","[CompanyID]=" & [CompanyID],"/")


    Public Function BuildValuesList(ByVal strTblName As String, _
    ByVal strValueField As String, _
    ByVal strWhere as String, _
    Optional strSeparator As String = ";") As String
    'Created by Charlotte Foust 1/6/001
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strValues As String
    Dim fldText As DAO.Field
    Dim strSQL As String

    strSQL = "SELECT * FROM " & strTblName & " " & strWhere
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(strWhere, dbOpenSnapshot)
    With rst
    Set fldText = .Fields(strValueField)
    Do While Not .EOF
    strValues = strValues & fldText & strSeparator
    .MoveNext
    Loop
    End With
    If strValues <> "" Then
    strValues = Left(strValues, Len(strValues) - 1)
    End If
    Set fldText = Nothing
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    BuildValuesList = strValues
    End Function
    Charlotte

Posting Permissions

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