Access 2013 中的 DMedian，未返回任何值

我在 MS Access 2013 中有以下查询，但不返回中值。

字段 IU 始终为 NULL（空白）或 1。列 GM 是一个介于 -5 和 5 之间、格式为 0.0000 的数字，其中偶尔会出现 NULL 值。

SELECT IU, DMedian("GM","tblFirst250","IU=1") AS MedianByIU
FROM tblFirst250
WHERE IU = 1
GROUP BY IU;

但是，在尝试调试此问题时，当我使用 DAvg 而不是 DMedian 时，我得到了平均值。

我从以下位置获得了 DMian（因为我知道 Access 2013 没有它）：https://msdn.microsoft.com/en-us/library/dd789431(v=office.12).aspx https://msdn.microsoft.com/en-us/library/dd789431(v=office.12).aspx

这是我数据库中名为 basDMedian 的模块中的代码：

Public Function DMedian( _
 ByVal strField As String, ByVal strDomain As String, _
 Optional ByVal strCriteria As String) As Variant

    ' Purpose:
    '     To calculate the median value
    '     for a field in a table or query.
    ' In:
    '     strField: the field.
    '     strDomain: the table or query.
    '     strCriteria: an optional WHERE clause to
    '                  apply to the table or query.
    ' Out:
    '     Return value: the median, if successful;
    '                   Otherwise, an Error value.

    Dim db As DAO.Database
    Dim rstDomain As DAO.Recordset
    Dim strSQL As String
    Dim varMedian As Variant
    Dim intFieldType As Integer
    Dim intRecords As Integer

    Const errAppTypeError = 3169

    On Error GoTo HandleErr

    Set db = CurrentDb()

    ' Initialize return value.
    varMedian = Null

    ' Build SQL string for recordset.
    strSQL = "SELECT " & strField & " FROM " & strDomain

    ' Only use a WHERE clause if one is passed in.
    If Len(strCriteria) > 0 Then
        strSQL = strSQL & " WHERE " & strCriteria
    End If

    strSQL = strSQL & " ORDER BY " & strField

    Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)

    ' Check the data type of the median field.
    intFieldType = rstDomain.Fields(strField).Type
    Select Case intFieldType
    Case dbByte, dbInteger, dbLong, _
      dbCurrency, dbSingle, dbDouble, dbDate
        ' Numeric field.
        If Not rstDomain.EOF Then
            rstDomain.MoveLast
            intRecords = rstDomain.RecordCount
            ' Start from the first record.
            rstDomain.MoveFirst

            If (intRecords Mod 2) = 0 Then
                ' Even number of records.
                ' No middle record, so move to the
                ' record right before the middle.
                rstDomain.Move ((intRecords \ 2) - 1)
                varMedian = rstDomain.Fields(strField)
                ' Now move to the next record, the
                ' one right after the middle.
                rstDomain.MoveNext
                ' And average the two values.
                varMedian = _
                  (varMedian + rstDomain.Fields(strField)) / 2
                ' Make sure you return a date, even when
                ' averaging two dates.
                If intFieldType = dbDate And Not IsNull(varMedian) Then
                    varMedian = CDate(varMedian)
                End If
            Else
                ' Odd number or records.
                ' Move to the middle record and return its value.
                rstDomain.Move ((intRecords \ 2))
                varMedian = rstDomain.Fields(strField)
            End If
        Else
            ' No records; return Null.
            varMedian = Null
        End If
    Case Else
        ' Non-numeric field; so raise an app error.
        Err.Raise errAppTypeError
    End Select

    DMedian = varMedian

ExitHere:
    On Error Resume Next
    rstDomain.Close
    Set rstDomain = Nothing
    Exit Function

HandleErr:
    ' Return an error value.
    DMedian = CVErr(Err.Number)
    Resume ExitHere
End Function

我的目标是获取 IU = 1 的记录的 GM 列的中值。我得到的值是 NULL/空白。如果有更好的方法，请告诉我。谢谢！

问你的DMedian表达式忽略任何空值GM field.

SELECT IU, DMedian("GM","tblFirst250","IU=1 AND GM Is Not Null") AS MedianByIU
FROM tblFirst250
WHERE IU = 1
GROUP BY IU;

基本上发生的事情是DMedian打开按以下顺序排序的记录集GM然后（大致）移动记录集的一半以获得中值。但如果该行中的值碰巧为 Null...

GM
--
Null
Null  <- median
2

... DMedian告诉你中位数为空。所以指导一下DMedian仅加载行GM Is Not Null给你非空值的中位数。