Implement Median grouped by 2 columns filtered by date in crosstab query

[tnmsr2010]

Need help implementing way to obtain Median grouped by 2 columns and filtered by date in a Crosstab query.

We use Access as front end reporting tool with linked tables to SQL Server 2008 backend db. Management wants a time analysis report of each performer by median (in seconds) per PO lines of invoices with 1 to 10 lines only. We will later be doing a volume analysis to account for invoices with lines greater than 10. I kinda have the DMedian VBA function I got from MSDN site working, but I can’t figure out how to:

1. group by PERFORMER and then by NUM_LINES in the set strSQL = … line(s) of code in the VBA module(in seconds)
2. call DMedian function from within the Crosstab query.

I have attached (zipped per rules for new register users with less than 10 posts) a temporary db I threw together for sole purpose of trying to explain and use as test to get this Median issue worked out. The table, tblmedian_test_data, was dump of some test data from actual db. The crosstab query, qry_median_avg_test_1, is where I need to get this median thing worked out.
The db has a few VBA functions I started to play with trying to figure this out. The one I got from MSDN was commented better than any of the others I’d seen but at this point I don’t care which one we use. I just need to get this working. Lastly I am including the sql for the crosstab query and the VBA code for the DMedian function. Again there are other functions in db but this is one I’ve been trying to get to work.

I welcome any and all help!

[FONT=Times New Roman]TRANSFORM Nz(Avg(DMedian("SECONDS","tbl_median_test_data")),0) AS CrossTabAvgMedian[/FONT]
[FONT=Times New Roman]SELECT tbl_median_test_data.NUM_LINES[/FONT]
[FONT=Times New Roman]FROM tbl_median_test_data[/FONT]
[FONT=Times New Roman]WHERE (((tbl_median_test_data.APPROVE_DATE)>=#1/1/2013# And (tbl_median_test_data.APPROVE_DATE)<=#1/14/2013#))[/FONT]
[FONT=Times New Roman]GROUP BY tbl_median_test_data.NUM_LINES[/FONT]
[FONT=Times New Roman]ORDER BY tbl_median_test_data.NUM_LINES, tbl_median_test_data.PERFORMER[/FONT]
[FONT=Times New Roman]PIVOT tbl_median_test_data.PERFORMER;[/FONT]

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
        strSQL = strSQL & " GROUP BY " & 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