Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Columnar Formatting within a Report Text field (XP/03)

    I have a report that its last act ("of defiance") is to include in the report footer the names of those that were good folks during the reporting period and did not have any records to report. I used the following code to populate a text field in the report footer:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim RcrdCount As Integer
    strSQL = "SELECT strName, numCountMnth " & _
    "FROM tblMyBestTable " & _
    "WHERE numCountMnth =0;"

    Me.txtNoRecords = ""

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset) ' rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    With rst
    rst.MoveLast
    RcrdCount = rst.RecordCount
    rst.MoveFirst
    Me.txtNoRecords= rst!strSiteName
    rst.MoveNext
    For i = 2 To rst.RecordCount
    Me.txtNoRecords= Me.txtNoRecords& " " & rst!strName
    rst.MoveNext
    Next i
    strSQL = ""
    End With

    Although not very elegant it accomplished what she asked for. Unfortunately what she asked for and what she wanted were not the same thing. The spaces inserted prior to inserting the Name ensures adequate separation between the names. However, since the list of names exceeds the width of the field the names in the second and subsequent lines do not line up in a tabular format, which is what she wants.

    Is there an elegant or other method to accomplish this?

    Thanks in advance for your thoughts and suggestions.

    Ken

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

    Re: Columnar Formatting within a Report Text field (XP/03)

    Instead of a text box, you could create a report based on the SQL statement. In the Columns tab of File | Page Setup, set the number of columns to 3 or whatever you want, and specify Across then Down as printing order.
    Place this report as subreport on the main report.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Columnar Formatting within a Report Text field (XP/03)

    Wow. I didn't realize you could do columnar reports that way. As usual, Hans, you are awesome!

    Thanks for your suggestion. It worked great.

Posting Permissions

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