Access World Forums - function for row number and/or conditional grouping

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)

- Code Repository (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=63)

- - function for row number and/or conditional grouping (https://www.access-programmers.co.uk/forums/showthread.php?t=307138)

function for row number and/or conditional grouping

Hopefully the notes are self explanatory. This function will return a row number based on a PK or other unique field or a group number based on some grouping value such as an FK. Note the value does not have to be number, it could be a date, text or boolean.

It handles the situation where similar functions might recalculate when the user selects a row. It is also extremely fast when compared with sql equivalents.

Just copy and paste the whole function into a standard module. Note the highlighted WHERE clause is required to initialise the collection. If not included you will get an error (one for each row) the first time you run it and subsequently an incorrect result.

The function can only be used once in any query (including sub queries/referenced queries) otherwise you will get incorrect results. Similarly if you require two group numbers in a single query (e.g. a row number and a grouping based on an FK) you will need a copy of the function with a different name (Perhaps call one rowNumber?;))

Queries run consecutively should not be a problem so long as one query finishes before the next one starts.

The query does not need to be sorted but obviously makes sense for grouping.

The returned value can be selected and the column sorted - values are not recalculated unless a record is added or deleted and the recordset requeried. Some may see this as an advantage, others a disadvantage but it is what it is.

Code:

Function GroupNumber(Optional v As Variant = -1) As Long

'Provides a group number to provide a value for conditionally formatting groups of rows or just as a row number

'For conditional formatting use something like 'expression is....groupnumber mod 2' for alternate groups, mod 3 for triple groups etc

'use pk or other unique field for the v parameter for row number otherwise whatever value forms a basis for the grouping - such as week number or FK

'use in query like this

'SELECT *, GroupNumber(datepart('ww',mydate)) as GroupWeek

'FROM myTable

'WHERE GroupNumber() = True

'Order By myDate 



'***** WARNING *****

'after the query has been executed, the colGroup collection will remain in memory. It is advisable to call GroupNumber (in VBA) without a parameter to free up the memory afte the query is run e.g.

'...

'...

'rs=currentdb.openrecordset("SELECT....

'GroupNumber

'...

'...



Static L As Long 'group number accumulator

Static colGroup As Collection 'of v's where a number has already been assigned

Dim t As Long 'temp value



    If v = -1 Then 'initialise static values (called by the where clause)

    

        Set colGroup = New Collection

        L = 0

        GroupNumber = -1 'out of range and -1 returns true

    

    Else



        On Error GoTo addToCollection

        t = colGroup(CStr(v)) 'if in collection use the existing group value - this prevents groupnumbers being recalculated when a row is selected by the user

        

        GroupNumber = t

        

    End If

    

    Exit Function

    

addToCollection:

    

        Select Case Err

            Case 91

                

                MsgBox "colGroup not initialised - include 'GroupNumber()=true' in your query WHERE clause", vbCritical + vbOKOnly, "Error in query call"

                t = 0

                

            Case Else

            

                L = L + 1

                colGroup.Add L, CStr(v) 'to ensure type consistency

                t = L

                

        End Select



        Resume Next

    

End Function