Microsoft Office Access-中位数功能-参数太少 [英] Microsoft Office Access - Median function - Too few parameters

问题描述

我正在尝试使用以下代码从具有以下条件的查询中计算中位数: <[Form]![testForm2]![crit1] And >[Form]![testForm2]![crit2] and <[Form]![testForm2]![Age1] And >[Form]![testForm2]![Age2]

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]

没有这些标准功能的情况就很好，并且为每个任务提供基于"MP"的中位数，但是当我在其中输入标准时，会收到错误消息: 错误-参数太少.应该为4，然后显示未设置对象变量或With块"

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'

我的输入: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

推荐答案

解决方案是引用SQL声明中的文本框，谢谢

Solution was to refer the text boxes in SQL declaration, Thank you guys

像这样:

HAVING (((Data.[REV]< " & Me.crit1 & ") And (Data.[REV])>" & Me.crit2 & ") AND ((Reg.Age)<" & Me.Age1 & " And (Reg.Age)>" & Me.Age2 & " " & SQLcritComplete & "));"

不喜欢这样:

"HAVING (((Data.[REV]<[Form]![testForm2]![crit1]) And (Data.[REV])>[testForm2]![crit2]) AND ((Reg.Age)<[Form]![testForm2]![Age1] And (Reg.Age)>[Form]![testForm2]![Age2] & SQLcritComplete & "));"

这篇关于Microsoft Office Access-中位数功能-参数太少的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！