Calculating medians in Access 2003 report

[Karen D]

Hello Experts!

I am running a report on a query in Access 2003 and I need to get the median(s) for several different fields in the footer of the report. I am currently using this solution from Microsoft (http://support.microso ft.com/kb/210581):

I pasted

 
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
 
 

into a new module and then placed a text box in the footer of the report with the Control Source property set to

=Median("<Table Name>", "<FieldName >")

When I run the report, I get a Parameter Value prompt for Median and then #Error# instead of a value in the report. I've tried several different but similar codes in the module but each time I get the same result. Any suggestions for correcting this problem? What am I doing wrong? Thanks!

[nico5038]

The "Parameter Value prompt" indicates something is wrong with your fieldname.
When using spaces in a fieldname (best not to) be sure to have surrounding [ and ] around the fieldname!

Nic;o)

[Karen D]

I don't have any spaces in the fieldname ("FC_1"). The record values for this field are showing correctly throughout the detail of the report and other functions, such as Count(FC_1), are producing appropriate results in the report footer. Thanks!

[nico5038]

Strange, I've used the function in my test database in a report group footer and it's returning the Median without any trouble.

Best to start with a compact and repair of your database.

Nic;o)

[Karen D]

No Change. The only thing else I can think to note is that I'm placing the function in the report footer rather than a group footer. I tried adding it to a group footer but it didn't change the result.

[nico5038]

Can you attach a (part of) the not working .mdb so I can check it?

Nic;o)

[Karen D]

I'm not sure how to attach it to this post but I've pasted a small piece of the database from the query below (the top row contains the field names):

Sample Date FC_1 FC_2 FC_3 FC_4 FC_5
7/19/2010 2.9 2.9 3.6 9.1 93
6/7/2010 2.9 3.6 3.6 9.1 23
4/21/2010 2.9 2.9 2.9 2.9 3.6
3/11/2010 2.9 2.9 2.9 2.9 2.9
12/14/2009 3.6 2.9 3.6 2.9 15
11/2/2009 3.6 2.9 43 93 93
10/13/2009 3.6 2.9 2.9 2.9 240
8/27/2009 9.1 2.9 7.3 2.9 3.6
7/16/2009 2.9 2.9 3.6 2.9 2.9
6/1/2009 2.9 2.9 2.9 2.9 14
4/21/2009 2.9 2.9 2.9 2.9 7.3
3/19/2009 2.9 2.9 2.9 3 2.9

This is just a small subset of both the fields and records in the actual query database. I need to obtain the median for each of the "FC_*" fields at the end of my report. I should point out that some of the records in each field are blank, so I'm also dealing with null values that I want to exclude from the median calculation. If I can get this function working, it should provide the results I need. Thanks!

[MMcCarthy]

Hi Karen

To attach the file. Zip it up and when posting click on the Go Advanced button. Scroll down to Manage Attachments.

Mary

[nico5038]

Please attach a sample with the table and the not working report. I've tested the function with your data and all works well.....

Nic;o)

[Karen D]

Much better - thanks for your help! I've created a subset of relevant files in the attached database. The report I'm having the problem with is Area48Cd. Please let me know if you need anything else. Thanks again!