Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
 
 
Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
Old 10-01-2019, 05:04 AM   #1
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,344
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.


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

__________________
CJ_London
_______________________
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
 

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping number field into intervals in a query Ksel Queries 5 07-02-2014 09:28 PM
DLookup function in report (using grouping) BCote689 Reports 1 01-09-2009 10:38 AM
report by grouping with substr function sunil3071 Reports 7 05-19-2008 09:00 PM
Grouping Data by Week Number choward Forms 1 03-04-2008 05:19 AM
grouping two tables which have different number of data mhisma Queries 2 09-23-2007 09:07 PM




All times are GMT -8. The time now is 02:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World