Statistical Median Code

[BarbaraH.]

I inherited a module from someone who no longer works here.
I'm trying to use it to no avail.
Can anyone figure out what is wrong? I've compaed it to
the knowledge base artcile 95918 that discuss using code
to derive a statistical median and it seems ok.
The error I'm getting is on the Openrecordset line for
setting the value of ssMedian. It is telling me "Too few
parameters. Expected 1".
Not much of a coder or debugger, so any help would be
appreciated. Here's the code for the module.
===============

Option Explicit

Function MEDIANX(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 & "] 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
MEDIANX = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For I = 0 To OffSet
ssMedian.MovePrevious
Next I
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
MEDIANX = (x + y) / 2
End If
ssMedian.Close
MedianDB.Close
End Function

===============
Regards,
Barbara

 

[Ronald Dodge]

That's cause with the OpenRecordset Method would be best defined with what
type of recordset, what type of permission on the recordset, and what sort
of lock, such as including the following within the parantheses after the
SQL statement as the source of the recordset:

<ddbDAODB>.OpenRecordset(SQLStmt,dbOpenDynaset,dbSeeChanges,dbPessimistic)

 

[Tim Ferguson]

It is telling me "Too few parameters. Expected 1".
....

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

This error is practically always caused by having a misspelled field name:
in your case whatever is being passed as fldName -- I suggest you check the
code that is calling this function and check that whatever is in fldName is
a valid field in the table referred to be tName.

Incidentally, although the use of 'ssMedian' suggests that the programmer
wanted to use a Snapshot, he has not actually put that into the
OpenRecordset method, so you will be using a Dynaset-type recordset which
is slower and will also be locking out other users. This is probably not a
big deal unless you are calling this code a lot.

Hope that helps


Tim F

 

[BarbaraH.]

Tim - your answer was correct.
I had an incorrect fieldname in the query calling the
module.
Thanks,
Barbara