Median and Undefined Function Error

[thatlem]

I have a module with the Microsoft suggested median function as below:

Function Median(tName As String, fldName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
If Not ssMedian Is Nothing Then
ssMedian.Close
Set ssMedian = Nothing
End If
Set MedianDB = Nothing
End Function


I then embedded the following SQL in a query:

SELECT Median("[qry_Betty Physician LL Detail Summary]","[DTB]") AS Expr1
FROM [qry_Betty Physician LL Detail Summary]
GROUP BY Median("[qry_Betty Physician LL Detail Summary]","[DTB]");

However, when I run the query I get an error: Undefined Function Median in query.

Any suggestion would help.
Thanks

 

[spikepl]

Public Function Median

 

[pbaldy]

Also make sure the code is in a standard module, not behind a form or something.

Edit: also make sure the module doesn't have the same name.

 

[thatlem]

Both of those ideas helped. Thanks.

However, I now have a new error code,

.....To Few Parameters, Expect 2..... Runtime Error 3061.

Debugging places the error in this section.

Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")


I also stripped out some of the [] in the SELECT and GROUP BY due to syntax issues...

SELECT Median("qry_Betty Physician LL Detail Summary","DTB") AS Expr1
FROM [qry_Betty Physician LL Detail Summary]
GROUP BY Median("qry_Betty Physician LL Detail Summary","DTB");

Any more ideas?

 

[vbaInet]

What is the name of the query where this function is run?

Also, can we see the SQL statement of qry_Betty Physician LL Detail Summary.

 

[thatlem]

The name of the query where this is run is: TEST Median

and the SQL for the source query is below:

SELECT [qry_Main Table Sort].[MD Table].Group, [qry_Main Table Sort].[MD Table].[Dr Name], [qry_Main Table Sort].[MD Cardiac], [qry_Main Table Sort].[Date of Stemi], Month([Date of Stemi]) AS [Month], IIf((Not (IsNull([Time of Diagnostic EKG]))),([Time wire crosses lesion]-[Time of Diagnostic EKG])*1440,IIf(((([time wire crosses lesion]-[time arrival to hosp])*1440)<0),((([time wire crosses lesion]-[time arrival to hosp])*1440)+1440),(([time wire crosses lesion]-[time arrival to hosp])*1440))) AS DTB, IIf((DateDiff("n",[Time STEMI called pager time],[Time MD in Lab])>0),(DateDiff("n",[Time STEMI called pager time],[Time MD in Lab])),((DateDiff("n",[Time STEMI called pager time],[Time MD in Lab]))+1440)) AS [MD Response], [qry_Main Table Sort].[PCI Type], [qry_Main Table Sort].[MR #], [qry_Main Table Sort].[Last Name], [qry_Main Table Sort].[First Name], [qry_Main Table Sort].[Time arrival to hosp], [qry_Main Table Sort].[Time of Diagnostic EKG], [qry_Main Table Sort].[Time STEMI called pager time], [qry_Main Table Sort].[Time MD in Lab], [qry_Main Table Sort].[Time wire crosses lesion], [qry_Main Table Sort].Intervention, [qry_Main Table Sort].Autokey, [qry_Main Table Sort].[Non-STEMI Patient]
FROM [qry_Main Table Sort]
GROUP BY [qry_Main Table Sort].[MD Table].Group, [qry_Main Table Sort].[MD Table].[Dr Name], [qry_Main Table Sort].[MD Cardiac], [qry_Main Table Sort].[Date of Stemi], IIf((DateDiff("n",[Time STEMI called pager time],[Time MD in Lab])>0),(DateDiff("n",[Time STEMI called pager time],[Time MD in Lab])),((DateDiff("n",[Time STEMI called pager time],[Time MD in Lab]))+1440)), [qry_Main Table Sort].[PCI Type], [qry_Main Table Sort].[MR #], [qry_Main Table Sort].[Last Name], [qry_Main Table Sort].[First Name], [qry_Main Table Sort].[Time arrival to hosp], [qry_Main Table Sort].[Time of Diagnostic EKG], [qry_Main Table Sort].[Time STEMI called pager time], [qry_Main Table Sort].[Time MD in Lab], [qry_Main Table Sort].[Time wire crosses lesion], [qry_Main Table Sort].Intervention, [qry_Main Table Sort].Autokey, [qry_Main Table Sort].[Non-STEMI Patient]
HAVING ((([qry_Main Table Sort].[Date of Stemi]) Between [Starting Date MM/DD/YY] And [Ending Date MM/DD/YY]) AND (([qry_Main Table Sort].Intervention)="yes") AND (([qry_Main Table Sort].[Non-STEMI Patient])=False))
ORDER BY [qry_Main Table Sort].[MD Table].Group, [qry_Main Table Sort].[MD Table].[Dr Name];


Thanks again.

 

[vbaInet]

It's complaining about these:

[Starting Date MM/DD/YY]
[Ending Date MM/DD/YY]

 

[thatlem]

So can I not run the Median function with this Between statement? I need to be able to limit the amount of data pulling back from the source table. Need more feedback.

Thanks

 

[vbaInet]

Those are input parameters aren't they? You are running a function that needs to be fed those dates.

You can remove the between parameter in the query and move it to the SQL statement in your function and concatenating the actual values there.

 

[raskew]

Hi -

Give this a try against the Northwind database. It works for me.

Best wishes - Bob

Function MedianF(pTable As String, pfield As String) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight") <enter.
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSql   As String
Dim n        As Integer
Dim sglHold  As Single

    strSql = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
    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
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

 

[thatlem]

Thanks for the feedback.

It was pointed out to me that I had two additional input statements from the source query that I was using to limit the amount of records pulled back that was causing the VB to issue an error code.

HAVING ((([qry_Main Table Sort].[Date of Stemi]) Between [Starting Date MM/DD/YY] And [Ending Date MM/DD/YY])....

Unfortunately, I have very little experience with VBA or SQL, and no formal training, so am a bit at a loss how to account for these additional inputs. I've given in a pretty good try, but without success, so if you could show me how to integrate these additional inputs into the VBA, that would be much appreciated.

Thanks in advance.

(I have learned so much from this forum over the past year - keep up the good work everyone )

 

[vbaInet]

Where would the Dates be coming from? You still want a prompt?

 

[thatlem]

Yes, I would still need to prompt the dates for the report.

The dates are user imputs from the underlying source query as: BETWEEN [Starting Date MM/DD/YY] AND [Ending Date MM/DD/YY]

The date imputs are pulling from:
Query: qry_Main Table Sort
Field: Date of Stemi

Thanks again.

 

[vbaInet]

You will need to convert your query to a form and set the Default View of your form to Datasheet. It looks exactly like a query. Once you've done that, let me know and I will tell you the next steps.

NB: By convert I mean, create a form based on your query, then take out the Date parameters.

 

[thatlem]

Just so we're clear and before we go much further, my intent here is to get the fundamental code so I can use a median within a query. I will eventually want to perform sorts and groupings, and use in reports. Just want you to understand my final goal before you spend a lot of time, so that if this takes us in a different direction we can stop now.

Ok, I have created a form, added the fields, set the default as dataset view, however, not sure what you mean about removing the date parameters. Are you asking me to remove the input request in the base query? Not clear how to remove those parameters from the form/dataset view.

Thanks for your patience.

 

[vbaInet]

In that case you will need to get the values from a pop-up form.

 

[vbaInet]

Actually, if you use this on a report, how many times will the Median function be called?