#Error results in Median query calling Median VBA function

[tnmsr2010]

Why am I receiving #Error when running my median query? Below are the code for 1) UserMedian query, 2) VBA MedianG code, and 3) visual of query results (once I end/sto debug "Run-time error '3061': Too few parameters. Expected 4." msg that appears. This median function and median query runs just fine in my local test db, but will not run in our live/prod db. I know I need to eventually add some error handling within VBA function code, but does anyone have any suggestions, ideas, thoughts why I'm getting the #Error results?

SELECT qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"1") AS Line1Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"2") AS Line2Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"3") AS Line3Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"4") AS Line4Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"5") AS Line5Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"6") AS Line6Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"7") AS Line7Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"8") AS Line8Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"9") AS Line9Median, MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"10") AS Line10Median
FROM qry_MLMStatsLineCountTimeDurationDetail10Lines
GROUP BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER
ORDER BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER;

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
'*******************************************
Dim Db As DAO.Database
Dim rs       As DAO.Recordset
Dim strSQL   As String
Dim n        As Double
Dim sglHold  As Long
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
    Set rs = Db.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

 

[tnmsr2010]

Reviewing Immediate window after Debug Run-time error '3061': Too few parameters. Expected 4 message comes up, has all the individual Performer/Line Select Stmts I need. Furthermore, when I grab any of them and bounce back into Access it runs just fine. I don't get it. As always I will keep working away and could really use some advice from the experts here.

 

[spikepl]

You have something complex that gives errors - do you know which bit? Dec0mplexify it!

1. Gut your Median function to nothing -comment everything out and let it produce, say, a result of 1. If you still get the error than its source is elsewhere. If not then start commenting stuff back in.

2. Remove bits form you SQL until it runs without error .. etc etc

3061 error appears most often when ppl run SQL from VBA and forget that Expression Service (the bit that interprets Access-object specific things for SQL) is not available, so SQL has no clue about anything you defined in Access. However, that fact that things run in one environment but not the other scuppers thius interpretation. DEbug it the oldfashioned way -. deconstruct and reconstruct - take a copy of your live db and play with it.

 

[tnmsr2010]

Thanks. And yes that is what I'm fixing to do - debugging as well as comparing to TEST db which runs just fine and give me exact results I need. I've also been in contact with server admins and DBAs as I've been getting ODBC connection failures this week which may or may not be contributing to immediate issue. Thanks again for your quick response. I appreciate it!

 

[tnmsr2010]

Pretty sure it has to do with parameter query even though I've changed that to feed parameter values from form. I'll keep digging ...

 

[spikepl]

feed parameter values from form, ...

Huh? Did you read what I wrote aboute Expression Service? The only way to set parameters' values - from VBA - for a stored query is by either changing the SQL using the QueryDef object, or by setting the values of Parameters (belonging to that query def) in code. All this implies that your production version is not 100% mirror of the test version, but that you have fiddled here and there. And now you pay

 

[tnmsr2010]

Yes I read it but since I have no idea what "ppl" is or what Expression Service is I didn't understand what you were saying. Upon re-reading your previous post and this one mentioning "QueryDef" I get the idea. Thank you for clarifying!

And yes the TEST Access file does not mirror PROD file. I am confused on how exactly to modified the VBA MedianG function code to include the QueryDef and how that impacts or does not impact the SQL being generated within function itself in the "strSQL = ..." lines of code. All QueryDef examples I've reviewed so far have something like:

Dim qdef As QueryDef
Set qdef = db.QueryDef(qry_MLMStatsLineCountTimeDurationDetail10Lines)
qdef.Parameters(0)="valuefor1stparameter"
qdef.Parameters(1)="valuefor2ndparameter"
...set any additional parameters...

I will proceed to 1) identify why it works in Test Access file but not in Prod Access file, and 2) try to figure how to add QueryDef code to existing MedianG VBA code.

Thank you for your help. Any help clarifying setting a QueryDef vs. how my function is currently setting the strSQL would be most welcome.

 

[tnmsr2010]

Is it possible to dynamically set a query parameter value in VBA code by referencing a control on a form? Or does that tie back to the Expression Service ("the bit that interprets Access-object specific things for SQL") not being available for VBA code to use?

I still need to figure out how to include QueryDef within the MedianG VBA function code, but for example when setting the parameter values could I use something like:

Dim qdef As QueryDef
Set qdef = db.QueryDef(qry_MLMStatsLineCountTimeDurationDetai l10Lines)
qdef.Parameters(0)="[Forms]![frm_Main]![StartDate]"
qdef.Parameters(1)="[Forms]![frm_Main]![ENDDate]"
qdef.Parameters(0)="[Forms]![frm_Main]![PERFORMER]"
...set any additional parameters...

Or do I have to do it like:

Dim qdef As QueryDef
Set qdef = db.QueryDef(qry_MLMStatsLineCountTimeDurationDetai l10Lines)
qdef.Parameters(0)="12/14/2012"
qdef.Parameters(1)="12/31/2012"
qdef.Parameters(0)="%"
...set any additional parameters...

Thank you.

 

[tnmsr2010]

Ah boy o boy what a lesson learned. Turns out 80+ percent of my issues was caused by ODBC connectivity inconsistency with domain I connect from and our production servers being on different domain and primarily a timeout issue caused by Access's default 60 seconds setting.

This thread is closed. Thanks to your reply and suggestions. I was able to resolve my issue changing timeout setting and by implementing some Public global variables after reading a post by DCrake. Thank you!