Newsgroup: it.comp.appl.access
Titolo: Generalizzare funzione Mediana

Ho trovato questa funzione che calcola la mediana dei valori di un
recordset.
Function fctnMediana(strField As String) As Variant
'Code sample from Accessory 
http://www22.brinkster.com/accessory
 'reference to the DAO Object Library too
    Dim dbs As dao.Database, rst As dao.Recordset, booEven As Boolean
'You
need to add a
    Set dbs = DBEngine(0)(0)
    Set rst = dbs.OpenRecordset("SELECT * FROM siti  WHERE unita =
'aaa'
ORDER BY " & strField)
    If rst.EOF = False Then
        rst.MoveLast
        booEven = (rst.RecordCount Mod 2 = 0) 'Is there an even number
of
records in the recordset?
        rst.PercentPosition = 50 'Rounds down if there is an even
number of
records...
        fctnMediana = rst.Fields(strField)
        If booEven Then
            rst.MoveNext
            fctnMediana = (fctnMediana + rst.Fields(strField)) / 2
'...so
take the average of
            'the this and the next value up
        End If
    End If
 rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function
Dal momento che vorrei utilizzarla in maniera dinamica il fatto che
come parametri voglia sempre il nome tabella il suo utilizzo mi risulta
poco pratico. Dovrei popolare sempre la tabella temporanea oppure
modificarla ogni volta.
Ho pensato di modificarla generalizzandola, vorrei infatti poterla
richiamare in qualunque parte del codice, query o report che voglio.
Ho pensato di riferirmi al recordsetclone e a codecontextobject ... ma
mi sono un po' impastato. Qualcuno mi sa indirizzare sulla giusta via?
Non esistendo in maniera nativa in access potrebbe sempre tornare
utile.
Public Function fctnMedian3(strTable As String, strField As String) As
Variant
  On Error GoTo erh
        Dim dbs As DAO.Database, rst As DAO.Recordset, booEven As
Boolean 'You must add a reference to the DAO Object Library too
        Set dbs = DBEngine(0)(0)
         '  Set rst = dbs.RecordsetClone
      With CodeContextObject.RecordsetClone
    If .EOF = False Then
        .MoveLast
        booEven = (.RecordCount Mod 2 = 0) 'Is there an even number of
records in the recordset?
        .PercentPosition = 50 'Rounds down if there is an even number
of records...
        fctnMedian3 = .Fields(strField)
        If booEven Then
            .MoveNext
            fctnMedian3 = (fctnMedian3 + .Fields(strField)) / 2 '...so
take the average of the this and the next value up
        End If
    End If
    End With
 
ext:
  Exit Function
erh:
  fctnMedian3 = 0
End Function

ciroteo ha scritto:

scusate! ho postato la funzione cosi come modificata in un post di
google ... l'originale presa dal sito sarebbe cosi:
Function fctnMedian(strTable As String, strField As String) As Variant
'Code sample from Accessory 
http://www22.brinkster.com/accessory
    Dim dbs As DAO.Database, rst As DAO.Recordset, booEven As Boolean
'You must add a reference to the DAO Object Library too
    Set dbs = DBEngine(0)(0)
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTable & " ORDER
BY " & strField)
    If rst.EOF = False Then
        rst.MoveLast
        booEven = (rst.RecordCount Mod 2 = 0) 'Is there an even number
of records in the recordset?
        rst.PercentPosition = 50 'Rounds down if there is an even
number of records...
        fctnMedian = rst.Fields(strField)
        If booEven Then
            rst.MoveNext
            fctnMedian = (fctnMedian + rst.Fields(strField)) / 2 '...so
