median

[יריב החביב]

Hello,

When i use this code on a table it is work,

but when i use it on a query it do'nt.

what should i do for using this code on query ? .....

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

THANKS A LOT

 

[Douglas J. Steele]

I don't see any reason why that shouldn't work.

When you say it doesn't work, what do you mean? Do you get an error? If so,
what's the error? If you don't get an error, what do you get and what do you
think you should get instead?

 

[יריב החביב]

thank you,

i get run-time error '3061'

too few parameters. expected 1.



--
תודה רבה

I don't see any reason why that shouldn't work.

When you say it doesn't work, what do you mean? Do you get an error? If so,
what's the error? If you don't get an error, what do you get and what do you
think you should get instead?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

When i use this code on a table it is work,

but when i use it on a query it do'nt.

what should i do for using this code on query ? .....

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

THANKS A LOT

 

[Douglas J. Steele]

Does the query have parameters? (Does it perhaps refer to a control on a
form?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thank you,

i get run-time error '3061'

too few parameters. expected 1.



--
תודה רבה

I don't see any reason why that shouldn't work.

When you say it doesn't work, what do you mean? Do you get an error? If
so,
what's the error? If you don't get an error, what do you get and what do
you
think you should get instead?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

When i use this code on a table it is work,

but when i use it on a query it do'nt.

what should i do for using this code on query ? .....

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

THANKS A LOT

 

[Ken Sheridan]

If you use the OpenRecordset method of the QueryDef object you can evaluate
the query's parameters. By setting the Sort property of the recordset you
can then create a new sorted recordset from the original, so you can then
iterate through it in the way your current function does. Here's a simple
procedure which demonstrates this by accepting a query and column name as its
arguments and then listing the sorted values in the column to the debug
window:

Public Sub SortQueryRecordset(strQuery As String, strColumn As String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rst_sorted As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQuery)

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset
rst.Sort = strColumn

Set rst_sorted = rst.OpenRecordset

Do While Not rst_sorted.EOF
Debug.Print rst_sorted(strColumn)
rst_sorted.MoveNext
Loop

End Sub

Ken Sheridan
Stafford, England

thank you,

i get run-time error '3061'

too few parameters. expected 1.



--
תודה רבה

I don't see any reason why that shouldn't work.

When you say it doesn't work, what do you mean? Do you get an error? If so,
what's the error? If you don't get an error, what do you get and what do you
think you should get instead?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

When i use this code on a table it is work,

but when i use it on a query it do'nt.

what should i do for using this code on query ? .....

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

THANKS A LOT

 

[Ken Sheridan]

PS: To filter out the NULLs add the following line to set the Filter
property of the first recordset before creating the sorted one:

rst.Filter = strColumn & " IS NOT NULL"

End Sub

Ken Sheridan
Stafford, England

 

[יריב החביב]

YES

The query presents sum for each month,which i have to evaluate the median
from

under a condition that the month's to show in the query

are only the month's that are before the month who presented on the form,

like this:

<[forms]![Ttichkoor_netoonim]![combo24]

when i canceling this, the function work.

but i need this condition.

how to combine the condition with the function ?

--
תודה רבה

Does the query have parameters? (Does it perhaps refer to a control on a
form?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thank you,

i get run-time error '3061'

too few parameters. expected 1.



--
תודה רבה

I don't see any reason why that shouldn't work.

When you say it doesn't work, what do you mean? Do you get an error? If
so,
what's the error? If you don't get an error, what do you get and what do
you
think you should get instead?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello,

When i use this code on a table it is work,

but when i use it on a query it do'nt.

what should i do for using this code on query ? .....

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

THANKS A LOT

 

[יריב החביב]

thank you

i understand what you mean.

but i dont understand how to implement it.

in other words, where to put this 'public sub' ?

 

[Ken Sheridan]

The sub procedure was only intended as an example to show you how the code
works. You'd put it in any standard module. To implement the code within
your function you'd need to create the recordset differently depending upon
whether you are using a table or a query. You could do this by first seeing
if the value of tName is in the database's TableDefs collection; if it is
it’s a table, if not it’s a query (or it doesn't exist, in which case an
error would be raised). Then you'd execute the relevant code to create the
recordset, and then proceed as before. So the amended code would be like
this:

Function Median (tName As String, fldName As String) As Single

Dim MedianDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer

Set MedianDB = CurrentDB()

' attempt to return a reference to table tName;
' if this raises an error then its not a table, so
' it must be a query (or not exist at all)
On Error Resume Next
Set tdf = MedianD.TableDefs(tName)
If Err.Number = 0 Then
' no error so use code for table
On Error Goto 0
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.
Else
' error raised, so use code for query
On Error Goto 0
Set qdf = MedianDB.QueryDefs(tName)

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset
rst.Sort = strColumn
rst.Filter = fldName & " IS NOT NULL"

Set ssMedian = rst.OpenRecordset
End If

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

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Oops! I made a couple of errors changing my original code to fit into yours.
It should have been:

Function Median(tName As String, fldName As String) As Single

Dim MedianDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer

Set MedianDB = CurrentDb()

' attempt to return a reference to table tName;
' if this raises an error then its not a table, so
' it must be a query
On Error Resume Next
Set tdf = MedianDB.TableDefs(tName)
If Err.Number = 0 Then
' no error so use code for table
On Error GoTo 0
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.
Else
' error raised, so use code for query
On Error GoTo 0
Set qdf = MedianDB.QueryDefs(tName)

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset
rst.Sort = fldName
rst.Filter = fldName & " IS NOT NULL"

Set ssMedian = rst.OpenRecordset
End If

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

Ken Sheridan
Stafford, England

 

[יריב החביב]

THANK YOU VERY VERY MUCH

 

[James A. Fortune]

Hello,

When i use this code on a table it is work,

but when i use it on a query it do'nt.

what should i do for using this code on query ? .....

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

THANKS A LOT

I offer the following as an alternative method:

In:

http://groups.google.com/group/comp.databases.ms-access/msg/1f2f7b4979f359ef

I cobbled together a non-VBA SQL method for computing a median.

In:

[Median of GROUP BY values]

http://groups.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d

I extended it to be able to calculate medians of grouped data.

James A. Fortune
(e-mail address removed)

 

[יריב החביב]

Hello Ken,

When i use this code on a value for series of month's, it work fine.

But now I want to evaluate the median for every 'unit' for series of
month's, it's not

what i am doing it's group by unit and put the code, but i am getting one
and the

same result (median) for all of the unit's.

can you help more in this point.

thank's

yariv

 

[Douglas J. Steele]

Are you saying that you're trying to apply a Where condition to your
calculation?

You could change the declaration to

Function Median(tName As String, fldName As String, SQLCondition As String)
As Single

and the SQL statement to

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

You'd then call the function like

Median("SomeField", "SomeTable", "SomeOtherField = 5")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Ken,

When i use this code on a value for series of month's, it work fine.

But now I want to evaluate the median for every 'unit' for series of
month's, it's not

what i am doing it's group by unit and put the code, but i am getting one
and the

same result (median) for all of the unit's.

can you help more in this point.

thank's

yariv
--
???? ???

Oops! I made a couple of errors changing my original code to fit into
yours.
It should have been:

Function Median(tName As String, fldName As String) As Single

Dim MedianDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer

Set MedianDB = CurrentDb()

' attempt to return a reference to table tName;
' if this raises an error then its not a table, so
' it must be a query
On Error Resume Next
Set tdf = MedianDB.TableDefs(tName)
If Err.Number = 0 Then
' no error so use code for table
On Error GoTo 0
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.
Else
' error raised, so use code for query
On Error GoTo 0
Set qdf = MedianDB.QueryDefs(tName)

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset
rst.Sort = fldName
rst.Filter = fldName & " IS NOT NULL"

Set ssMedian = rst.OpenRecordset
End If

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

Ken Sheridan
Stafford, England

 

[יריב החביב]

thank you very much for your replay


--
תודה רבה

Hello,

When i use this code on a table it is work,

but when i use it on a query it do'nt.

what should i do for using this code on query ? .....

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

THANKS A LOT

I offer the following as an alternative method:

In:

http://groups.google.com/group/comp.databases.ms-access/msg/1f2f7b4979f359ef

I cobbled together a non-VBA SQL method for computing a median.

In:

[Median of GROUP BY values]

http://groups.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d

I extended it to be able to calculate medians of grouped data.

James A. Fortune
(e-mail address removed)

 

[יריב החביב]

thank you

i combine your solution with the code and it is dont work

can you see what i am missing ?

Function Median(tName As String, fldName As String, SQLCondition As String)
As Single

Dim MedianDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer

Set MedianDB = CurrentDb()

' attempt to return a reference to table tName;
' if this raises an error then its not a table, so
' it must be a query
On Error Resume Next
Set tdf = MedianDB.TableDefs(tName)
If Err.Number = 0 Then
' no error so use code for table
On Error GoTo 0
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL AND " & SQLCondition & _
" ORDER BY [" & fldName & "];")

'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
Else
' error raised, so use code for query
On Error GoTo 0
Set qdf = MedianDB.QueryDefs(tName)

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset
rst.Sort = fldName
rst.Filter = fldName & " IS NOT NULL"

Set ssMedian = rst.OpenRecordset
End If

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

--
תודה רבה

Are you saying that you're trying to apply a Where condition to your
calculation?

You could change the declaration to

Function Median(tName As String, fldName As String, SQLCondition As String)
As Single

and the SQL statement to

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

You'd then call the function like

Median("SomeField", "SomeTable", "SomeOtherField = 5")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello Ken,

When i use this code on a value for series of month's, it work fine.

But now I want to evaluate the median for every 'unit' for series of
month's, it's not

what i am doing it's group by unit and put the code, but i am getting one
and the

same result (median) for all of the unit's.

can you help more in this point.

thank's

yariv
--
???? ???

Oops! I made a couple of errors changing my original code to fit into
yours.
It should have been:

Function Median(tName As String, fldName As String) As Single

Dim MedianDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer

Set MedianDB = CurrentDb()

' attempt to return a reference to table tName;
' if this raises an error then its not a table, so
' it must be a query
On Error Resume Next
Set tdf = MedianDB.TableDefs(tName)
If Err.Number = 0 Then
' no error so use code for table
On Error GoTo 0
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.
Else
' error raised, so use code for query
On Error GoTo 0
Set qdf = MedianDB.QueryDefs(tName)

For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset
rst.Sort = fldName
rst.Filter = fldName & " IS NOT NULL"

Set ssMedian = rst.OpenRecordset
End If

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

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

Rather than me trying to debug that code, see whether the code from my
October, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access" works for you. You can download the column (and sample database) for
free at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[יריב החביב]

THANK YOU

I implement your code

i get run time error 3061 - too few parameter. expected 3

 

[Douglas J. Steele]

If you're trying to use a query that includes parameters (as opposed to a
table), you'll need to include the code from the previous model that
resolves the parameters.

 

[יריב החביב]

OK

NOW I USE A TABLE AND YOUR CODE.

i want to get the spesific median for every unit

but

it give me the same resulte (median) for all of the units