View Single Post
Old 10-02-2019, 10:58 PM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: function for row number and/or conditional grouping

look at that I already posted similar code to help an OP in sept-2:
Code:
Public Function fncList(ByVal p_id As Long, ByVal p_max_rows As Integer) As Integer
    '
    ' arnelgp
    ' 02-sep-2019
    '
    ' p_id      autonumber value or -99.
    '           if -99 is passed, it will reset the collection object.
    ' purpose:
    '
    ' group records in Query
    '
    ' syntax: "select table1.*, fncList([ID]) As Grouping From table1;"
    '
    Const NO_ERROR      As Integer = 0
    Static col_values   As VBA.Collection
    Static int_counter  As Integer
    Static m_max_row    As Integer
    Dim int_ret         As Integer
    If (p_id = -99) Then
        Set col_values = New VBA.Collection
        int_counter = 0
        Exit Function
    End If
    If (col_values Is Nothing) Or (p_max_row <> m_max_row) Then
        Set col_values = New VBA.Collection
        m_max_row = p_max_row
        int_counter = 0
    End If
    On Error Resume Next
    int_ret = col_values(p_id & "")
    If err.Number <> NO_ERROR Then
        err.Clear
        On Error GoTo 0
        int_counter = int_counter + 1
        col_values.Add (int_counter \ (p_max_rows + 1)) + 1, p_id & ""
        int_ret = (int_counter \ (p_max_rows + 1)) + 1
    End If
    fncList = int_ret
End Function

Public Function fncResetList()
Call fncList(-99)
End Function
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote