CODEWITHSUNDEEP
vbaexcelms-access
sigil
sigil

Reputation: 9546

How to get the median of an Access data set imported into Excel?

Access doesn't have a native median function, so I'm using this UDF:

Function fMedian(SQLOrTable, GroupFieldName, GroupFieldValue, MedianFieldName)
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(SQLOrTable, dbOpenDynaset)

    If Not (GroupFieldName = "" And GroupFieldValue = "") Then
        If IsDate(GroupFieldValue) Then
            GroupFieldValue = "#" & GroupFieldValue & "#"
        ElseIf Not IsNumeric(GroupFieldValue) Then
            GroupFieldValue = "'" & Replace(GroupFieldValue, "'", "''") & "'"
        End If

        rs1.Filter = GroupFieldName & "=" & GroupFieldValue
    End If

    rs1.Sort = MedianFieldName

    Set rs = rs1.OpenRecordset()
    rs.Move (rs.RecordCount / 2)

    If rs.RecordCount Mod 2 = 0 Then
        varMedian1 = rs.Fields(MedianFieldName)
        rs.MoveNext
        fMedian = (varMedian1 + rs.Fields(MedianFieldName)) / 2
    Else
        fMedian = rs.Fields(MedianFieldName)
    End If

End Function

And then using the function as in the following example query medianQuery:

select fMedian("someTable","aGroupField",[aGroupField],"medianField") from someTable

The problem is that I'm trying to import the results of medianQuery into an Excel report as follows (VBA in an Excel workbook):

Sub importData(db As DAO.Database)

Dim rs As DAO.Recordset

Set rs = db.OpenRecordset(recordsetName, Options:=dbReadOnly)

End Sub

This produces the error:

Run-time error '3085':

Undefined function 'fmedian' in expression.

I'm aware that the problem here is that Excel can't read Access queries that use UDFs. So how can I pull this Access data into Excel and still have a median?

Upvotes: 0

Views: 321

Answers (1)

Rob Anthony
Rob Anthony

Reputation: 1813

If you modify your Access query to Create a new table, all the data you want will be stored in this table, you can then attach Excel to this table. As Excel is just pulling through pre-calculated numbers, you will have no problem.

Upvotes: 0

Related Questions