Calculating the median

Giganews Newsgroups
Subject: Calculating the median
Posted by:  peacockā€¦@stu.otc.school.nz
Date: Sun, 8 Apr 2018

I've found the code below from another discussion, but it keeps asking for a Parameter value. When input there is no value. Is there anyway to fix this? I'm new to access

Function Median(tName As String, fldName As String) As Double
'Calculates the statistical median. To use this function type:
' =Median("DomainName", "FieldName")
'Domain name is the name of a query or table
'FieldName should be a numeric field.

  Dim MedianDB As DAO.Database
  Dim ssMedian As DAO.Recordset
  Dim RCount As Long, i As Long, x As Double, y As Double, OffSet As Long

  Set MedianDB = CurrentDb()
  Set ssMedian = MedianDB.OpenRecordset( _
        "SELECT [" & fldName & "]" & _
        " FROM [" & tName & "]" & _
        " WHERE [" & fldName & "] IS NOT NULL" & _
        " ORDER BY [" & fldName & "]")

  If ssMedian.RecordCount > 0 Then
      ssMedian.MoveLast

      RCount = ssMedian.RecordCount
      x = RCount Mod 2 'Determine if even or odd # of records

      If x <> 0 Then  'Odd number of records
        OffSet = (RCount - 1) \ 2
        ssMedian.Move -OffSet
        median = ssMedian(fldName)

      Else 'Even number of records
        OffSet = (RCount / 2) - 1
        ssMedian.Move -OffSet
        x = ssMedian(fldName)
        ssMedian.MovePrevious
        y = ssMedian(fldName)
        median = (x + y) / 2
      End If

  End If 'records in recordset

  ssMedian.Close
  MedianDB.Close

End Function

Replies