VBA Median function works in Test not in Live (1 Viewer)

[tnmsr2010]

I have a MedianG VBA function in a local test dbname.accdb that I call from a query to get Median for User1Line1, User1Line2, User1Line3 ... up to User1Line10. Works beautifully in test db. Today I copied VBA function/module into our live front-end dbname.accdb. I then took sql from qry_User1Medians and replaced "tbl_median_test_data" with "qry_MLMStatsLineCountTimeDurationDetail10Lines" and "BN" for "REALUSER NAME" and now it doesn't work. Only difference is in my local test db I am calling a table whereas in my live CR01Report db I'm calling a query. Would that cause problems?

I don't get it. VBA code is exactly same and SQL query is exactly same except for table vs query and dummy user initials vs real user name substitutions. I've double/triple checked to make sure the VBA referenced librarys (Tools -> Reference) are checked the same in both local db and live db. I will keep plugging away and would appreciate any assistance you can provide.

I receive message, "You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)." I don't understand why I don't get this in local test but do in live db.

Here is SQL for qry_User1Medians in local TEST db:

SELECT tbl_median_test_data.PERFORMER, tbl_median_test_data.NUM_LINES, MedianG("tbl_median_test_data","SECONDS","BN","1") AS User1Line1Median, MedianG("tbl_median_test_data","SECONDS","BN","2") AS User1Line2Median, MedianG("tbl_median_test_data","SECONDS","BN","3") AS User1Line3Median, MedianG("tbl_median_test_data","SECONDS","BN","4") AS User1Line4Median, MedianG("tbl_median_test_data","SECONDS","BN","5") AS User1Line5Median, MedianG("tbl_median_test_data","SECONDS","BN","6") AS User1Line6Median, MedianG("tbl_median_test_data","SECONDS","BN","7") AS User1Line7Median, MedianG("tbl_median_test_data","SECONDS","BN","8") AS User1Line8Median, MedianG("tbl_median_test_data","SECONDS","BN","9") AS User1Line9Median, MedianG("tbl_median_test_data","SECONDS","BN","10") AS User1Line10Median
FROM tbl_median_test_data
WHERE (((tbl_median_test_data.APPROVE_DATE)>=#1/1/2013# And (tbl_median_test_data.APPROVE_DATE)<=#1/14/2013#))
GROUP BY tbl_median_test_data.PERFORMER, tbl_median_test_data.NUM_LINES
HAVING (((tbl_median_test_data.PERFORMER)="BN"))
ORDER BY tbl_median_test_data.PERFORMER, tbl_median_test_data.NUM_LINES;

Here is same sql for qry_User1Medians in CR01Report LIVE db:

SELECT qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","1") AS User1Line1Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","2") AS User1Line2Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","3") AS User1Line3Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","4") AS User1Line4Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","5") AS User1Line5Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","6") AS User1Line6Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","7") AS User1Line7Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","8") AS User1Line8Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","9") AS User1Line9Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","REALUSER NAME","10") AS User1Line10Median
FROM qry_MLMStatsLineCountTimeDurationDetail10Lines
WHERE (((qry_MLMStatsLineCountTimeDurationDetail10Lines.APS_DATE)>=#12/14/2012# And (qry_MLMStatsLineCountTimeDurationDetail10Lines.APS_DATE)<=#1/14/2013#))
GROUP BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES
HAVING (((qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER)="REALUSER NAME"))
ORDER BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES;

Here is the VBA MedianG function:

Option Compare Database
Function MedianG(ptable As String, pfield As String, Optional pgroup1 As String, Optional pgroup2 As String) As Long
'###########################################
'Purpose:   Return median value from a recordset
'Inputs:    MedianG("tableorqueryname", "numericfieldnametogetmedian", "performervalue", "linesvalue") <enter>
'Output:    ideally the median value per performer/lines combination
'###########################################
'New
Dim Db As DAO.Database
'Change
Dim rs       As DAO.Recordset
'Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Double
Dim sglHold  As Long
'New
Set Db = CurrentDb()
    'If pgroup1 provided then add it to strSQL
    If Len(pgroup1) > 0 Then
        strSQL = "SELECT " & pfield & " from " & ptable & " WHERE PERFORMER = '" & pgroup1 & "' Order by " & pfield & ";"
        'If pgroup2 provided then add it to strSQL
        If Len(pgroup2) > 0 Then
            strSQL = "SELECT " & pfield & " from " & ptable & " WHERE PERFORMER = '" & pgroup1 & "' AND NUM_LINES = " & pgroup2 & "  Order by " & pfield & ";"
        End If
    Else
        strSQL = "SELECT " & pfield & " from " & ptable & " Order by " & pfield & ";"
    End If
    Debug.Print strSQL
'Change
    Set rs = Db.OpenRecordset(strSQL)
    'Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    If n Mod 2 = 1 Then 'odd number of elements
       MedianG = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianG = sglHold / 2
    End If
    
    rs.Close
End Function

Any help would be greatly appreciated.

 

[spikepl]

So how do you know it is not the
qry_MLMStatsLineCountTimeDurationDetail10Lines query itself that renders the error?

 

[tnmsr2010]

Good point. But I run that query by itself just fine with no problems.

 

[tnmsr2010]

Did little more digging/investigating last night/this morning. Seems it has nothing to do with calling the VBA function with a table vs. query, but rather the fact that our live production query uses parameters. When I tested this in our test db calling query with no parameters works fine, however, calling it with query that has parameters returns "Run-time Error '3061' Too few parameters. Expected 2."

Need to figure out way/how to get this to work in production where we have to use querries and parameters. I may create the parameter form to see if referencing a parameter control value on form (similiar to how I've set it up in other dbs).

Again, any thoughts, suggestions, or advice are welcome and appreciated.

 

[tnmsr2010]

I've gone ahead and created parameter form to eliminate parameter value prompts when qry_MLMStatsLineCountTimeDurationDetail10Lines is ran. However, I am still receiving, "You tried to execute a query that does not include the specified expression 'qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER="BN"' as part of an aggregate function." message when running qry_User1Medians.

Can anyone provide advice? Why is the following query returning above error message?

Here is qry_User1Medians sql:

SELECT qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","1") AS User1Line1Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","2") AS User1Line2Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","3") AS User1Line3Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","4") AS User1Line4Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","5") AS User1Line5Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","6") AS User1Line6Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","7") AS User1Line7Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","8") AS User1Line8Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","9") AS User1Line9Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS","BN","10") AS User1Line10Median
FROM qry_MLMStatsLineCountTimeDurationDetail10Lines
GROUP BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES
HAVING (((qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER)="BN"))
ORDER BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, qry_MLMStatsLineCountTimeDurationDetail10Lines.NUM_LINES;

 

[tnmsr2010]

Just wanting to close this loop and sent a thank you to DCrake for his/her posting I found in thread on this site about Public Variables. I was able to resolve my issue. Thank you, DCrake!!!