Median in a report

[Scott]

I need to take the median from a field of records in a report. Can someone
shed the light how to do it.

Thanks,

Scott

[Gord]

Re: Median in a report



On Jan 29, 11:54 am, "Scott" <NoSpam-Scott...@GMail. comwrote:

I need to take the median from a field of records in a report. Can someone
shed the light how to do it.

Probably. Go to

http://groups.google.ca/group/comp.databases.ms-access

and then search for the word "median". Review the 115 results. Then,
if you still need help, follow-up with a more specific question.

[Scott]

Re: Median in a report

Gord,

Thanks for your advice. I got below code and apply to my report without
success.

Function Median(tName As String, fldName As String) As Double
'Calculates the statistical median. To use this function type:
' =Median("Domain Name", "FieldName" )
'Domain name is the name of a query or table
'FieldName should be a numeric field.


Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Long, i As Long, x As Double, y As Double, OffSet As Long


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


If ssMedian.Record Count 0 Then
ssMedian.MoveLa st


RCount = ssMedian.Record Count
x = RCount Mod 2 'Determine if even or odd # of records


If x <0 Then 'Odd number of records
OffSet = (RCount - 1) \ 2
ssMedian.Move -OffSet
Median = ssMedian(fldNam e)


Else 'Even number of records
OffSet = (RCount / 2) - 1
ssMedian.Move -OffSet
x = ssMedian(fldNam e)
ssMedian.MovePr evious
y = ssMedian(fldNam e)
Median = (x + y) / 2
End If


End If 'records in recordset


ssMedian.Close
MedianDB.Close


End Function


The report is based on a query. I need to have the median in Category
Footer of the report and the field is a calculated field from the query. I
entered =Median("qryTEL eadtime","Queui ngTime") in controlsource of the
textbox created in the Category Footer. I got an error message saying that
"The expression you entered contains invalid syntax. You may have entered an
operand without an operator." Can someone point me out where the mistake
might be.

Thanks,

Scott

"Gord" <gdt@kingston.n etwrote in message
news:1170093295 .858166.217880@ k78g2000cwa.goo glegroups.com.. .

>
>
On Jan 29, 11:54 am, "Scott" <NoSpam-Scott...@GMail. comwrote:

>I need to take the median from a field of records in a report. Can
>someone
>shed the light how to do it.

>
Probably. Go to
>

http://groups.google.ca/group/comp.databases.ms-access

>
and then search for the word "median". Review the 115 results. Then,
if you still need help, follow-up with a more specific question.
>

[Gord]

Re: Median in a report

>I entered =Median("qryTEL eadtime","Queui ngTime") in controlsource of

>the textbox created in the Category Footer. I got an error message
>saying that "The expression you entered contains invalid syntax. You
>may have entered an operand without an operator." Can someone point
>me out where the mistake might be.

Well,

=Median("Domain Name", "FieldName" )

works for me.

Check for typos.


On Jan 31, 1:12 pm, "Scott" <NoSpam-Scott...@GMail. comwrote:

Gord,
>
Thanks for your advice. I got below code and apply to my report without
success.
>
Function Median(tName As String, fldName As String) As Double
'Calculates the statistical median. To use this function type:
' =Median("Domain Name", "FieldName" )
'Domain name is the name of a query or table
'FieldName should be a numeric field.
>
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Long, i As Long, x As Double, y As Double, OffSet As Long
>
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRe cordset( _
"SELECT [" & fldName & "]" & _
" FROM [" & tName & "]" & _
" WHERE [" & fldName & "] IS NOT NULL" & _
" ORDER BY [" & fldName & "]")
>
If ssMedian.Record Count 0 Then
ssMedian.MoveLa st
>
RCount = ssMedian.Record Count
x = RCount Mod 2 'Determine if even or odd # of records
>
If x <0 Then 'Odd number of records
OffSet = (RCount - 1) \ 2
ssMedian.Move -OffSet
Median = ssMedian(fldNam e)
>
Else 'Even number of records
OffSet = (RCount / 2) - 1
ssMedian.Move -OffSet
x = ssMedian(fldNam e)
ssMedian.MovePr evious
y = ssMedian(fldNam e)
Median = (x + y) / 2
End If
>
End If 'records in recordset
>
ssMedian.Close
MedianDB.Close
>
End Function
>
The report is based on a query. I need to have the median in Category
Footer of the report and the field is a calculated field from the query. I
entered =Median("qryTEL eadtime","Queui ngTime") in controlsource of the
textbox created in the Category Footer. I got an error message saying that
"The expression you entered contains invalid syntax. You may have entered an
operand without an operator." Can someone point me out where the mistake
might be.
>
Thanks,
>
Scott
>
"Gord" <g...@kingston. netwrote in message
>
news:1170093295 .858166.217880@ k78g2000cwa.goo glegroups.com.. .

On Jan 29, 11:54 am, "Scott" <NoSpam-Scott...@GMail. comwrote:

I need to take the median from a field of records in a report. Can
someone
shed the light how to do it.

>

Probably. Go to

>

http://groups.google.ca/group/comp.databases.ms-access

>

and then search for the word "median". Review the 115 results. Then,
if you still need help, follow-up with a more specific question.