View Single Post
Old 10-01-2019, 05:04 AM   #1
Super Moderator
Join Date: Feb 2013
Location: UK
Posts: 11,347
Thanks: 40
Thanked 3,670 Times in 3,538 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
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.

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.

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

        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
        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

A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 10-01-2019 at 05:23 AM.
CJ_London is offline   Reply With Quote