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]

Test Database for Problem Median

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!

[nico5038]

Hmm, the problem is the fact that your "table" is a parameter query.
As the Median function activates the query, the same parameters are needed.

To solve this I've added a form for printing the report, a query that's dynamically filled with the parameter values and the report parms are now referring to the fields on the new form.

Just check it out.

Nic;o)

[Karen D]

Wow! Highly impressive and very, very much appreciated!! I was definitely at a dead end without your help! Thanks so much!

[nico5038]

Glad I could help.
Just make sure that it's the intention to have multiple years accumulated, as only selecting a month in a multiple year table could give "strange" effects !
It's not clear from the report which years are included...
Just a point of concern :-)

Nic;o)

[Karen D]

These are water quality samples. The analysis requires a minimum of 30 (non-blank) samples at each station but we only take about six samples per year so multiple years are necessary. Some areas are only open to harvesting seasonally (e.g. November to May) so we're only looking at the water quality during that part of the year, again over multiple years. I'm curious as to why the Median function would set off the parameter request when the other built-in functions don't. I have several similar queries/reports for other areas and I don't want to fall into the same trap (although now I'll have a solution to turn to!) Thanks!

[nico5038]

The problem is the fact hat the Median function opens the query again, thus asking for the parameters again...

A work around could be to use the recordset of the report, but that's only possible working with an ADP (Access Data Project).
An alternative might be to change the parameter query into a Createtable (and/or Append) query to store the data in a temporary table. Then the report and the function can work on that temp table.

Nic;o)