Please help

[Al]

I am trying to calculate the median with the following
function:
********************************
Function Medianfltr(TName As String, fldName As String,
Optional fltrName 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 and [" & fltrName & "]='" & [fltrName] &
"' 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
Medianfltr = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For I = 0 To OffSet
ssMedian.MovePrevious
Next I
X = ssMedian(fldName)
ssMedian.MovePrevious
Y = ssMedian(fldName)
Medianfltr = (X + Y) / 2
End If
ssMedian.Close
MedianDB.Close
End Function
********************************

originally, this function did not have the <Optional
fltrName As String)>, therefore it would calculate the
median for a recordset in its entirity. What I am
interested in is to calculate the median for different sub
sets of the record set and that is why I tryed to tweak it
a little bit by having the function calculate for sub sets
if fltrName was added. in other words if a field named
states was added on the report then the function would
calculate the median for NY,AL,TX,... instead of the median
for all the states. the problem that I am having is how to
substitute for the different values in the state fields so
that I get the median for each state. can someone help?
thanks (note the function works fine without the optional
fltername as string that I added later)
Al

 

[Marshall Barton]

Al, I didn't follow what you want the fltrName argument to
do here, but the query needs to be fixed. I'm pretty sure
that you meant for it to be this:

"] IS NOT NULL and [" & fldName & "]='" & [fltrName] &

The method you're using to get to the middle record is very
inefficient. Try using something more like this:

If X <> 0 Then
OffSet = (RCount + 1) / 2)
ssMedian.AbsolutePosition = OffSet
Medianfltr = ssMedian(fldName)
Else
OffSet = (RCount / 2)
ssMedian.AbsolutePosition = OffSet
X = ssMedian(fldName)
ssMedian.MoveNext
Y = ssMedian(fldName)
Medianfltr = (X + Y) / 2
End If
ssMedian.Close : Set ssMedian = Nothing
Set MedianDB = Nothing

Also note the change to set the object variables to Nothing
and since you did not open the db object, you should not try
to close it.
--
Marsh
MVP [MS Access]

I am trying to calculate the median with the following
function:
********************************
Function Medianfltr(TName As String, fldName As String,
Optional fltrName 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 and [" & fltrName & "]='" & [fltrName] &
"' 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
Medianfltr = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For I = 0 To OffSet
ssMedian.MovePrevious
Next I
X = ssMedian(fldName)
ssMedian.MovePrevious
Y = ssMedian(fldName)
Medianfltr = (X + Y) / 2
End If
ssMedian.Close
MedianDB.Close
End Function
********************************

originally, this function did not have the <Optional
fltrName As String)>, therefore it would calculate the
median for a recordset in its entirity. What I am
interested in is to calculate the median for different sub
sets of the record set and that is why I tryed to tweak it
a little bit by having the function calculate for sub sets
if fltrName was added. in other words if a field named
states was added on the report then the function would
calculate the median for NY,AL,TX,... instead of the median
for all the states. the problem that I am having is how to
substitute for the different values in the state fields so
that I get the median for each state. can someone help?
thanks (note the function works fine without the optional
fltername as string that I added later)
Al

 

[Al]

Say I wanted to calculate the median, on a report, for the
number of customers in a table. I would make a calculated
field with the following as its control source:
=Medianfltr("tblCustomers","No_OfCustomers"). This will
give me the median for the entire table. What I need is to
get the median for the number of customers per state, so
that I tried to add the Optional fltrName which in this
case will be "State" field so that to make fldName =
fltrName would not work as you put it in your reply since
fldName in this case would be the number of customers
field. I hope this is clear. There is another function that
I used that is better than this one but I still do not know
how to make it calculate the median per sub sets in a table
like in this case here per state. here it is:
**************************************************
Public Function MedianOfRst(RstName As String, fldName As
String) As Double
'This function will calculate the median of a
recordset. The field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName,
dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then 'Even
RstSorted.AbsolutePosition =
(RstSorted.RecordCount / 2) - 1
MedianTemp = Nz(RstSorted.Fields(fldName).value)
RstSorted.MoveNext
MedianTemp = Nz(MedianTemp +
RstSorted.Fields(fldName).value)
MedianTemp = MedianTemp / 2
Else 'Odd
RstSorted.AbsolutePosition =
(RstSorted.RecordCount - 1) / 2
MedianTemp = Nz(RstSorted.Fields(fldName).value)
End If
MedianOfRst = MedianTemp
End Function


***************************************************

-----Original Message-----
Al, I didn't follow what you want the fltrName argument to
do here, but the query needs to be fixed. I'm pretty sure
that you meant for it to be this:

"] IS NOT NULL and [" & fldName & "]='" & [fltrName] &

The method you're using to get to the middle record is very
inefficient. Try using something more like this:

If X <> 0 Then
OffSet = (RCount + 1) / 2)
ssMedian.AbsolutePosition = OffSet
Medianfltr = ssMedian(fldName)
Else
OffSet = (RCount / 2)
ssMedian.AbsolutePosition = OffSet
X = ssMedian(fldName)
ssMedian.MoveNext
Y = ssMedian(fldName)
Medianfltr = (X + Y) / 2
End If
ssMedian.Close : Set ssMedian = Nothing
Set MedianDB = Nothing

Also note the change to set the object variables to Nothing
and since you did not open the db object, you should not try
to close it.
--
Marsh
MVP [MS Access]

I am trying to calculate the median with the following
function:
********************************
Function Medianfltr(TName As String, fldName As String,
Optional fltrName 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 and [" & fltrName & "]='" & [fltrName] &
"' 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
Medianfltr = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For I = 0 To OffSet
ssMedian.MovePrevious
Next I
X = ssMedian(fldName)
ssMedian.MovePrevious
Y = ssMedian(fldName)
Medianfltr = (X + Y) / 2
End If
ssMedian.Close
MedianDB.Close
End Function
********************************

originally, this function did not have the <Optional
fltrName As String)>, therefore it would calculate the
median for a recordset in its entirity. What I am
interested in is to calculate the median for different sub
sets of the record set and that is why I tryed to tweak it
a little bit by having the function calculate for sub sets
if fltrName was added. in other words if a field named
states was added on the report then the function would
calculate the median for NY,AL,TX,... instead of the median
for all the states. the problem that I am having is how to
substitute for the different values in the state fields so
that I get the median for each state. can someone help?
thanks (note the function works fine without the optional
fltername as string that I added later)
Al

.

 

[Marshall Barton]

Say I wanted to calculate the median, on a report, for the
number of customers in a table. I would make a calculated
field with the following as its control source:
=Medianfltr("tblCustomers","No_OfCustomers"). This will
give me the median for the entire table. What I need is to
get the median for the number of customers per state, so
that I tried to add the Optional fltrName which in this
case will be "State" field so that to make fldName =
fltrName would not work as you put it in your reply since
fldName in this case would be the number of customers
field. I hope this is clear. There is another function that
I used that is better than this one but I still do not know
how to make it calculate the median per sub sets in a table
like in this case here per state. here it is:
**************************************************
Public Function MedianOfRst(RstName As String, fldName As
String) As Double
'This function will calculate the median of a
recordset. The field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName,
dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then 'Even
RstSorted.AbsolutePosition =
(RstSorted.RecordCount / 2) - 1
MedianTemp = Nz(RstSorted.Fields(fldName).value)
RstSorted.MoveNext
MedianTemp = Nz(MedianTemp +
RstSorted.Fields(fldName).value)
MedianTemp = MedianTemp / 2
Else 'Odd
RstSorted.AbsolutePosition =
(RstSorted.RecordCount - 1) / 2
MedianTemp = Nz(RstSorted.Fields(fldName).value)
End If
MedianOfRst = MedianTemp
End Function

When you say you want to "get the median for the number of
customers per state", it sounds like you want this function
to return multiple values, one value for each state. If
that's right, you have to explain how the calling procedure
is going to deal with all the return values??

On the other hand, if you want to calculate the median for a
single state, then add an argument to the function to
specify the entire filter string (just like DLookup).

Public Function MedianOfRst(RstName As String, _
fldName As String, fltrWhere As String) As Double
Dim RstSorted As Recordset
. . .
RstOrig.Sort = fldName
RstOrig.Filter = fltrWhere
Set RstSorted = RstOrig.OpenRecordset()

With this arangement, your function would behave much like a
Domain Aggregate (DSum, DAvg, etc) function. This could
then be used in a report that's grouped on the state field,
in another query, etc.

 

[Al]

Thank you Marsh for your help. here is what I did. I
incorporated what you wrote, the results as follow:
in the report I have one txtbox for state and one
calculated txtbox for the median which has the function as
follow: =MedianOfRst("tblCustomers","No_OfCustomers","State")
The results that I am expecting should be as follow:

State Median
Al 72
NY 80
TX 15

But what is happining is that the function runs the first
time and then carries over the first median through out the
report as follows:
State Median
Al 72
NY 72
TX 72

the median does not change by changing the state and this
was my original problem. I thought that the function would
recalculate every time the state's changed. both fields are
in the detail section of the report and I also tried to
group by the state and place the median box at the state
footer but still gave me the same result (72) all the way
through. How can I make it change when the state changes?
Al

 

[Marshall Barton]

Thank you Marsh for your help. here is what I did. I
incorporated what you wrote, the results as follow:
in the report I have one txtbox for state and one
calculated txtbox for the median which has the function as
follow: =MedianOfRst("tblCustomers","No_OfCustomers","State")
The results that I am expecting should be as follow:

State Median
Al 72
NY 80
TX 15

But what is happining is that the function runs the first
time and then carries over the first median through out the
report as follows:
State Median
Al 72
NY 72
TX 72

the median does not change by changing the state and this
was my original problem. I thought that the function would
recalculate every time the state's changed. both fields are
in the detail section of the report and I also tried to
group by the state and place the median box at the state
footer but still gave me the same result (72) all the way
through. How can I make it change when the state changes?

But you're not changing the state in the function call, it's
just a string with the word State. The way I suggested you
should call the function is:

=MedianOfRst("tblCustomers","No_OfCustomers", _
"State='" & statetextbox & "'")

Note that the fltrWhere argument needs to be a complete
where clause without the word "Where", just like the domain
aggregate functions.

To make your function look and feel exactly like all the
other domain aggregate functions, you should have the
fldName argument first and the domain name second, then no
one would be confused if you even went so far as to name the
function DMedian.
--
Marsh
MVP [MS Access]

 

[Al]

Thanks Marsh, I did, and it worked.
Al

-----Original Message-----

Thank you Marsh for your help. here is what I did. I
incorporated what you wrote, the results as follow:
in the report I have one txtbox for state and one
calculated txtbox for the median which has the function as
follow: =MedianOfRst("tblCustomers","No_OfCustomers","State")
The results that I am expecting should be as follow:

State Median
Al 72
NY 80
TX 15

But what is happining is that the function runs the first
time and then carries over the first median through out the
report as follows:
State Median
Al 72
NY 72
TX 72

the median does not change by changing the state and this
was my original problem. I thought that the function would
recalculate every time the state's changed. both fields are
in the detail section of the report and I also tried to
group by the state and place the median box at the state
footer but still gave me the same result (72) all the way
through. How can I make it change when the state changes?

But you're not changing the state in the function call, it's
just a string with the word State. The way I suggested you
should call the function is:

=MedianOfRst("tblCustomers","No_OfCustomers", _
"State='" & statetextbox & "'")

Note that the fltrWhere argument needs to be a complete
where clause without the word "Where", just like the domain
aggregate functions.

To make your function look and feel exactly like all the
other domain aggregate functions, you should have the
fldName argument first and the domain name second, then no
one would be confused if you even went so far as to name the
function DMedian.
--
Marsh
MVP [MS Access]


.