VBA Median function declared as string vs variant arguments gives inconsistent #Error

[tnmsr2010]

We use MS Access 2010 with Linked SQL Server 2008 tables. I have few reports working 99% of what Management wants/needs. I am currently testing to get reports to run if user(s) has no data.

When I declare function arguments as Strings, the MLM returns #Error for Medians and the Level2 returns a "blank" value (not sure if empty string or Null) for Medians. I thought wrapping Nz() around median calls would help but I get same results.

When I declare function arguments as Variants, I get a reversed results. The MLM returns a "blank" value whereas the Level2 returns #Error. Again, wrapping Nz() function around both median calls gives same result.

Why? What am I missing. How can I fix this behavior so both calls to the Median function return the "blank" value? I'm assuming I need to include some kind of check within the VBA function code, but I don't know VBA good enough to figure it out. Can someone help me out please?

Only thing different between the two function call querries is that the MLM uses additonal NUM_LINES optional argument whereas the Level2 query call does not. They both use optional PERFORMER argument.

Here is SQL for MLM call:

SELECT qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES, Nz(DMedian("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],[qry_MLMStatsLineCountTimeDurationDetail10Lines].[NUM_LINES])) AS UserLineMedian, Sum(qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES) AS TotalUserLines
FROM qry_MLMStatsLineCountTimeDurationDetail10Lines
GROUP BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES
ORDER BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES;

Here is SQL for Level2 call:

SELECT qry_Level2StatsInvoiceTimeDurationDetail.PERFORMER, Nz(DMedian("qry_Level2StatsInvoiceTimeDurationDetail","SECONDS",[qry_Level2StatsInvoiceTimeDurationDetail].[PERFORMER])) AS UserInvoiceMedian, Count(qry_Level2StatsInvoiceTimeDurationDetail.DOC_ID) AS TotalUserInvoices
FROM qry_Level2StatsInvoiceTimeDurationDetail
GROUP BY qry_Level2StatsInvoiceTimeDurationDetail.PERFORMER;

Here is the VBA DMedian function code:

'changed String arguments to Variant to test #Error if user(s) have no data
Public Function DMedian(strDomain As Variant, strField As Variant, Optional strGroup1 As Variant, Optional strGroup2 As Variant) As Variant
'Public Function DMedian(strDomain As String, strField As String, Optional strGroup1 As String, Optional strGroup2 As String) As Variant
    '*******************************************
    'Purpose:   Return median value for a field in a table or query recordset
    'Inputs:    strField: the field
    '           strDomain: the table or query
    '           strCriteria: an optional WHERE clause to apply to the table or query
    '           strGroup1: an optional GROUP BY clause to apply to the table or query
    '           strGroup2: an optional GROUP BY clause to apply to the table or query
    'Calling:   DMedian("numericfieldnametogetmedian", "tableorqueryname", "stringforwhereclause") <enter>
    'Output:    Returns 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 include group1 filter in WHERE clause if one is passed in
    If Len(strGroup1) > 0 Then
        strSQL = strSQL & " WHERE PERFORMER = '" & strGroup1 & "'"
        'use group2 as another filter in WHERE clause if one is passed in
        If Len(strGroup2) > 0 Then
            strSQL = strSQL & " AND NUM_LINES = " & strGroup2 & ""
        End If
    End If
    'added to see if it would help with slowness before deciding to use a MakeTable
    'strSQL = strSQL & " AND APS_DATE Between GetStartDate() And GetEndDate()"
    strSQL = strSQL & " ORDER BY " & strField
    
    'for displaying strSQL in Immediate Window for troubleshooting
    'Debug.Print strSQL
    
    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

 

[tnmsr2010]

The underlying source querries for the MLM and Level2 querries above that call VBA median function simply use temporary localized tables (make tables) since I never could figure out how to implement QueryDef within the VBA function code.

 

[gemma-the-husky]

when you say "strings" how big is the varchar field

maybe strings that are too long (like memo fields) do not lend themselves to some actions.

alternatively maybe it is to do with formatting the strings (and dates) to make them usable within a query, as a string/date needs different formatting to a numeric field.

 

[mdlueck]

since I never could figure out how to implement QueryDef within the VBA function code.

Here is some sample code utilizing DAO.QueryDef objects...

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605#post1119605

 

[tnmsr2010]

Thanks Gemma!

Performer field is defined as 255 character although longest value in either table (MLM or Level2) is 24 characters. I reduced 255 down to 25 and still get same results.

The NUM_LINES field is defined as Number (Long Integer). I'm wondering if 1) I need to declare the 2nd optional argument for VBA median function to LONG instead of String or Variant, OR 2) within the VBA code where the strSQL is being generated I need to make some kind of modification to allow for this number (Long Integer) to be passed as a string, or 3) convert this NUM_LINES number (Long Integer) data type into a string when calling/passing to function.

I'll keep at it. Thank you for your feedback and appreciate any other suggestions you have.

 

[tnmsr2010]

Thanks mdlueck!

I'll take a look at your other post to see if it kicks off any light bulbs regarding the use of QueryDef. Thanks!

 

[gemma-the-husky]

thinking again, I think the issue may be the optional arguments. an optional argument has to be checked with "ismissing"

however ismissing is only available to variant (or undeclared) record types. so ismissing on a string will return false, even when the parameter is actually missing, and the string will be allocated a default value of a zls instead.

I expect this is the problem


generally i always declare the value for an optional parameter

optional somefield as string = ""
optional somefield as long = 0

eg this link
http://msdn.microsoft.com/en-us/library/office/aa164532(v=office.10).aspx