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]

On Jan 29, 11:54 am, "Scott" <[e-mail]:

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]

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("DomainName", "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.OpenRecordset( _
"SELECT [" & fldName & "]" & _
" FROM [" & tName & "]" & _
" WHERE [" & fldName & "] IS NOT NULL" & _
" ORDER BY [" & fldName & "]")
If ssMedian.RecordCount 0 Then
ssMedian.MoveLast
RCount = ssMedian.RecordCount
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(fldName)
Else 'Even number of records
OffSet = (RCount / 2) - 1
ssMedian.Move -OffSet
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
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("qryTELeadtime","QueuingTime") 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" <gd*@kingston.netwrote in message
news:11**********************@k78g2000cwa.googlegr oups.com...

>

On Jan 29, 11:54 am, "Scott" <[e-mail]:

>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]

>I entered =Median("qryTELeadtime","QueuingTime") 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("DomainName", "FieldName")

works for me.

Check for typos.
On Jan 31, 1:12 pm, "Scott" <[e-mail]:

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("DomainName", "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.OpenRecordset( _
"SELECT [" & fldName & "]" & _
" FROM [" & tName & "]" & _
" WHERE [" & fldName & "] IS NOT NULL" & _
" ORDER BY [" & fldName & "]")

If ssMedian.RecordCount 0 Then
ssMedian.MoveLast

RCount = ssMedian.RecordCount
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(fldName)

Else 'Even number of records
OffSet = (RCount / 2) - 1
ssMedian.Move -OffSet
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
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("qryTELeadtime","QueuingTime") 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" <[e-mail] in message

news:11**********************@k78g2000cwa.googlegr oups.com...

On Jan 29, 11:54 am, "Scott" <[e-mail]:

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.