Microsoft Office Access - Median function - Too few parameters

Question

I am trying to use this code to calculate median from my query which has these criteria: <[Form]![testForm2]![crit1] And >[Form]![testForm2]![crit2] and <[Form]![testForm2]![Age1] And >[Form]![testForm2]![Age2]

without these criteria function works well and gives for every task median based on "MP", however when I put in there my criteria I receive error: error - Too few parameters. Expected 4 and then it says 'Object Variable or With block not set'

my input: DMedian("MP";"testForm2";"[TASK]= '" & [TASK] & "'")

*even when the Form is open it end up with the error. *I probably need to find a different way to filter this query from the form, but I don't know how

Public Function DMedian(FieldName As String, _
      TableName As String, _
      Optional Criteria As Variant) As Variant

' Created by Roger J. Carlson
' http://www.rogersaccesslibrary.com
' Terms of use: You may use this function in any application, but
' it must include this notice.

'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

' You use this function much like the built-in Domain functions
' (DLookUp, DMax, and so on). That is, you must provide the
' 1) field name, 2) table name, and 3) a 'Where' Criteria.
' When used in an aggregate query, you MUST add each field
' in the GROUP BY clause into the into the Where Criteria
' of this function.

' See Help for more on Domain Aggregate functions.

On Error GoTo Err_Median

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim RowCount As Long
    Dim LowMedian As Double, HighMedian As Double

    'Open a recordset on the table.
    Set db = CurrentDb
    strSQL = "SELECT " & FieldName & " FROM " & TableName
    If Not IsMissing(Criteria) Then
        strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName
    Else
        strSQL = strSQL & " ORDER BY " & FieldName
    End If
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    'Find the number of rows in the table.
    rs.MoveLast
    RowCount = rs.RecordCount
    rs.MoveFirst

    'Determine Even or Odd
    If RowCount Mod 2 = 0 Then
        'There is an even number of records. Determine the low and high
        'values in the middle and average them.
        rs.Move Int(RowCount / 2) - 1
        LowMedian = rs(FieldName)
        rs.Move 1
        HighMedian = rs(FieldName)
        'Return Median
        DMedian = (LowMedian + HighMedian) / 2
    Else
        'There is an odd number of records. Return the value exactly in
        'the middle.
        rs.Move Int(RowCount / 2)
        'Return Median
        DMedian = rs(FieldName)
    End If

Exit_Median:
    'close recordset
    rs.Close
    Exit Function

Err_Median:
    If Err.number = 3075 Then
        DMedian = 0
        Resume Exit_Median
    ElseIf Err.number = 3021 Then
        'EOF or BOF ie no recordset created
        DMedian = -1
        Resume Exit_Median
    Else
        MsgBox Err.Description
        Resume Exit_Median
    End If
End Function

Can't use dynamic parameterized query. The criteria must be included in function argument. — June7
I don't use dynamic parameterized queries. I apply filter to form or report. — June7
What do you mean by 'further calculations'? If you want DMedian to aggregate same records selected by those 4 parameters then you need to have the same 4 parameters in the function call. Use dynamic parameterized query for the form (or apply filter criteria to form/report) then have function reference table and execute with all necessary criteria. The criteria used by function can also reference form controls. Actually, should be able to include function call in that dynamic parameterized query. Again, function call would have to reference table and include all necessary criteria. — June7

HackSlash HackSlash · Accepted Answer · 2019-11-21T21:14:53

The parameter separation character is comma and you are using a semi-colon

CHANGE:

DMedian("MP";"testForm2";"[TASK]= '" & [TASK] & "'")

TO:

DMedian("MP", "testForm2", "[TASK]= '" & [TASK] & "'")

Microsoft Office Access - Median function - Too few parameters

2 Answers