Report data in rows not columns

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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 & “));”

Reply | Quote

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

Reply | Quote