1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
|
Public Function CompteOccurences(ByVal idRecord As Long, ByVal Value As Long) As Long
' 2019-08-05 MN
' Exemple usage : CompteOccurences(1,2)
Dim oDb As DAO.Database, oRs As DAO.Recordset, strsql As String, i As Long, n As Long
n = 0
Set oDb = CurrentDb
' Soit une table Table1 avec les colonnes Row_id, Fld01, Fld02, Fld03, Fld04, Fld05, Fld06, Fld07, Fld08, Fld09, Fld10
' les 10 colonnes Fld01 ...Fld10 sont des numériques
strsql = "SELECT * FROM Table1 WHERE Row_id =" & idRecord
Set oRs = oDb.OpenRecordset(strsql, dbOpenSnapshot)
With oRs
If .RecordCount > 0 Then
.MoveLast
.MoveFirst 'même s'il n'y en a qu'un, dans ce cas !
For i = 0 To .Fields.Count - 1
If Left(.Fields(i).Name, 3) = "Fld" And .Fields(i) = Value Then n = n + 1
Next i
End If
CompteOccurences = n
End With
End Function |
Partager