title: How to get the median of an Access data set imported into Excel?
body: <p>Access doesn't have a native median function, so I'm using this UDF:</p>
  
  <pre><code>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 = "#" &amp; GroupFieldValue &amp; "#"
          ElseIf Not IsNumeric(GroupFieldValue) Then
              GroupFieldValue = "'" &amp; Replace(GroupFieldValue, "'", "''") &amp; "'"
          End If
  
          rs1.Filter = GroupFieldName &amp; "=" &amp; 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
  </code></pre>
  
  <p>And then using the function as in the following example query <code>medianQuery</code>:</p>
  
  <pre><code>select fMedian("someTable","aGroupField",[aGroupField],"medianField") from someTable
  </code></pre>
  
  <p>The problem is that I'm trying to import the results of <code>medianQuery</code> into an Excel report as follows (VBA in an Excel workbook):</p>
  
  <pre><code>Sub importData(db As DAO.Database)
  
  Dim rs As DAO.Recordset
  
  Set rs = db.OpenRecordset(recordsetName, Options:=dbReadOnly)
  
  End Sub
  </code></pre>
  
  <p>This produces the error:</p>
  
  <pre><code>Run-time error '3085':
  
  Undefined function 'fmedian' in expression.
  </code></pre>
  
  <p>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?</p>