DMedian在获得2013年，没有返回值 [英] DMedian in access 2013, no values returned

问题描述

我在MS Access 2013下面的查询比不返回一个中间值。

I have the following query in MS Access 2013 than does not return a Median value.

本场IU总是NULL（空）或1列通用汽车是一个数字格式的0.0000 -5和5之间，NULL值是偶尔为之。

The field IU is always NULL (blank) or 1. Column GM is a number formatted 0.0000 between -5 and 5 with NULL values in it occasionally.

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

然而，在试图调试这一点，当我使用DAVG代替DMedian我得到一个平均值。

However, while attempting to debug this, when I use DAvg instead of DMedian I get an Average value.

我DMedian（因为我了解，访问2013没有它）来自： https://msdn.microsoft.com/en-美国/库/ dd789431（V = office.12）的.aspx

I got DMedian (because I understood that Access 2013 didn't have it) from: https://msdn.microsoft.com/en-us/library/dd789431(v=office.12).aspx

下面是code我有一个名为basDMedian作为我的数据库的一部分模块：

Here is the code I have in a module called basDMedian as part of my database:

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。我得到的值为空/空的所有记录的通用列的中值。如果有更好的方法，请让我知道。谢谢！

My objective is to get a Median value of the GM column of the records that have IU = 1. The value I get is NULL/blank. If there is a better approach, please let me know. Thanks!

推荐答案

问问你的 DMedian EX pression忽略任何空值在转基因字段。

Ask your DMedian expression to ignore any Nulls in the 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 通过记录开了一个记录集排序的通用汽车的再迁（大约）一半的方式来获得中间值。但是，如果在该行的值正好是空...

Basically what happened is DMedian opened a recordset sorted by GM and then moved (roughly) half way through the recordset to get the median value. But if the value in that row happened to be Null ...

GM
--
Null
Null  <- median
2

... DMedian 告诉你，中位数为NULL。所以指示 DMedian 只加载行，其中转IS NOT NULL 给你的非NULL值的中值。

... DMedian told you the median is Null. So instructing DMedian to load only rows where GM Is Not Null gave you the median of the non-Null values.

这篇关于DMedian在获得2013年，没有返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！