I have a Recordset in VBA , I want to count how many variants there are of a value.
That is, what in a query SQL would be:
SELECT COUNT (tag)
FROM table
GROUP BY tag
Table would be the Recordset and tag the value I want to know how many different types there are.
For example, if tag is: y1, y1, y1, y2, y3, y3, y4, y4, y5 I want to receive a 5, since there are 5 different values.
For this type of operation it is better to use dictionaries.
I've played with the object Scripting.Dictionary
And now the code:
'---->IMPORTANTE!!!: Hay que activar la referencia a Microsoft Scripting Runtime <----!!!!
Dim RST As Recordset 'variable Recordset
Dim Dict As Scripting.Dictionary 'nuestro diccionatio
Set Dict = New Scripting.Dictionary
Set RST = Application.CurrentDb.TableDefs("TABLA").OpenRecordset(1, 16)
RST.MoveLast
RST.MoveFirst
Do Until RST.EOF = True
If Dict.Exists(RST.Fields("Tag").Value) = False Then
'no existe, lo creamos
Dict.Add RST.Fields("Tag").Value, 1
Else
'ya existe, no hacemos nada.
End If
RST.MoveNext
Loop
Set RST = Nothing
Debug.Print Dict.Count 'total elementos diferentes
Set Dict = Nothing
In my case, I simulated a table in Ms-Access with the Tag field and introduced the values that you put in your example y1,y1,y1,y2,y3,y3,y4,y4,y5 , and the line Debug.Print Dict.Count is the one that returns the total of different elements. Be careful, because dictionaries give a lot of play. In this example, I have limited myself to do what you asked for, but in reality in the Else where we do not do anything, you can tell the dictionary to increment the account by 1 if the element exists.
That is, with the object Scripting.Dictionary you could easily get the following:
TAGS DIFERENTES: 5
Tag totales
y1 3
y2 1
y3 2
y4 2
y5 1
I hope you can adapt this code to your needs:)