Statistical Median Code

B

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
 
R

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)
 
T

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
 
B

BarbaraH.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Median in Report 1
Calculation for Median 3
median 31
Calculating median in a group by query 4
Median in Report 1
YES 0
Calculating the Median for specified groups 1
Please help 6

Top