Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to number rows in a report or query (Access02)

    Hi, thanks to all of you who answer questions!! I want to add a field only for reports or queries that gives each row a number, not each record. I know I read something about this recently but haven't been able to find it. Thanks,

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How to number rows in a report or query (Access02)

    What's a row, as opposed to a record?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to number rows in a report or query (Access02)


  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How to number rows in a report or query (Access02)

    If you want to number each LINE in report, rather than each RECORD, one method would be to use bogus running sum textboxes. Add an unbound textbox to each report section, set Running Sum property to "No". Then use the Print (not Format!!) event for each report section to simulate a continuous running sum for report:

    Option Compare Database
    Option Explicit

    Dim lngCount As Long

    Private Sub Report_Open(Cancel As Integer)
    lngCount = 1
    End Sub

    Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
    Me.txtRunSum1 = lngCount
    lngCount = lngCount + 1
    End If
    End Sub

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
    Me.txtRunSum2 = lngCount
    lngCount = lngCount + 1
    End If
    End Sub

    Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
    Me.txtRunSum3 = lngCount
    lngCount = lngCount + 1
    End If
    End Sub

    This will result in continuous "line count" for report. Note that in this example the Page Header & Footer are not counted. This assumes there is only one line for each detail record and each group header or footer. If any of these have more than one line, I know of no simple way to count each line individually. If report has additional grouping levels use similar code for each group header or footer. Small sample DB (A2K format) is attached with sample report illustrating this technique, using Northwind.mdb "Products" & related tables. The tables are linked to default location for Northwind.mdb in ACC 2002. If your copy of Northwind located elsewhere use Linked Table Manager to refresh links to correct location

    HTH
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to number rows in a report or query (Access02)

    Thank you all for your replies! I'm off and running...

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How to number rows in a report or query (Access02)

    As far as numbering each row (record) returned by a query, that is a bit trickier, I haven't found an efficient method for this. There was a recent thread that showed how to do this using DCount function, but this required a second query to get the row numbers. (See <!post=Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1),211002>Re: Running Sum Error In Query? (A2k (9.0.3821) SR-1)<!/post> )

    Here is an alternative method that opens a recordset in code based on same query that displays the record numbers. For this to work correctly, the query needs to be sorted by a field that will return unique values when query runs, such as a primary key or other field with a unique index . The function looks like this:

    Function GetRecordCount(strQry As String, strFld As String, _
    varVal As Variant, intType As Integer) As Long
    On Error GoTo Err_Handler

    ' strQry = name of query
    ' strFld = name of primary key or unique field used to sort query
    ' varVal = value of field in query to use for comparison
    ' intType = data type (1 = number, 2 = text)

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strMsg As String

    Select Case intType
    Case 1 ' Numerical
    strSQL = "SELECT * FROM <!t>[" & strQry & "]<!/t> " & _
    "WHERE <!t>[" & strFld & "]<!/t> <=" & varVal & " " & _
    "ORDER BY " & strFld & ";"
    Case 2 ' Text
    strSQL = "SELECT * FROM <!t>[" & strQry & "]<!/t> " & _
    "WHERE <!t>[" & strFld & "]<!/t> <='" & varVal & "' " & _
    "ORDER BY " & strFld & ";"
    End Select

    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL)
    With rst
    If Not .EOF Then
    .MoveLast
    End If
    GetRecordCount = .RecordCount
    End With

    Exit_Sub:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Function
    Err_Handler:
    Resume Exit_Sub

    End Function

    Example of use in query:

    SELECT Customers.CustomerID, Customers.ContactName, GetRecordCount("Query1","CustomerID",[CustomerID],2) AS [Record Count]
    FROM Customers
    ORDER BY Customers.CustomerID;

    In example "Query1" is name of query, "CustomerID" (Northwind "Customers" table) is primary key field used to sort query. There will be a slight delay when query opens because the function is executed for each row returned by query. This seems to be slightly more efficient than the DCount approach, and does not require a 2nd saved query; but there may be a better way to do this.

    HTH

Posting Permissions

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