473,322 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Calculating medians in Access 2003 report

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.microsoft.com/kb/210581):

I pasted

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function Median (tName As String, fldName As String) As Single
  3.   Dim MedianDB As DAO.Database
  4.   Dim ssMedian As DAO.Recordset
  5.   Dim RCount As Integer, i As Integer, x As Double, y As Double, _
  6.       OffSet As Integer
  7.   Set MedianDB = CurrentDB()
  8.   Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName & _
  9.             "] FROM [" & tName & "] WHERE [" & fldName & _ 
  10.             "] IS NOT NULL ORDER BY [" & fldName  & "];")
  11.   'NOTE: To include nulls when calculating the median value, omit
  12.   'WHERE [" & fldName & "] IS NOT NULL from the example.
  13.   ssMedian.MoveLast
  14.   RCount% = ssMedian.RecordCount
  15.   x = RCount Mod 2
  16.   If x <> 0 Then
  17.      OffSet = ((RCount + 1) / 2) - 2
  18.      For i% = 0 To OffSet
  19.         ssMedian.MovePrevious
  20.      Next i
  21.      Median = ssMedian(fldName)
  22.   Else
  23.      OffSet = (RCount / 2) - 2
  24.      For i = 0 To OffSet
  25.         ssMedian.MovePrevious
  26.      Next i
  27.      x = ssMedian(fldName)
  28.      ssMedian.MovePrevious
  29.      y = ssMedian(fldName)
  30.      Median = (x + y) / 2
  31.   End If
  32.   If Not ssMedian Is Nothing Then
  33.      ssMedian.Close
  34.      Set ssMedian = Nothing
  35.   End If
  36.   Set MedianDB = Nothing
  37. End Function
  38.  
  39.  
into a new module and then placed a text box in the footer of the report with the Control Source property set to

=Median("<TableName>", "<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!
Oct 6 '10 #1
14 2599
nico5038
3,080 Expert 2GB
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)
Oct 6 '10 #2
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!
Oct 6 '10 #3
nico5038
3,080 Expert 2GB
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)
Oct 6 '10 #4
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.
Oct 6 '10 #5
nico5038
3,080 Expert 2GB
Can you attach a (part of) the not working .mdb so I can check it?

Nic;o)
Oct 6 '10 #6
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!
Oct 6 '10 #7
MMcCarthy
14,534 Expert Mod 8TB
Hi Karen

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

Mary
Oct 6 '10 #8
nico5038
3,080 Expert 2GB
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)
Oct 6 '10 #9
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!
Attached Files
File Type: zip Area48test.zip (1.14 MB, 119 views)
Oct 6 '10 #10
nico5038
3,080 Expert 2GB
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)
Attached Files
File Type: zip area48_nico5038.zip (894.1 KB, 113 views)
Oct 6 '10 #11
Wow! Highly impressive and very, very much appreciated!! I was definitely at a dead end without your help! Thanks so much!
Oct 6 '10 #12
nico5038
3,080 Expert 2GB
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)
Oct 6 '10 #13
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!
Oct 7 '10 #14
nico5038
3,080 Expert 2GB
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)
Oct 7 '10 #15

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Jarrod | last post by:
I have multiple reports in one database, on one form. I need to know what reports were run when, and by Network User ID. How do I do that?
2
by: CSDunn | last post by:
Hello, I have an Access 2003 report that opens in print preview and then maximizes upon clicking a button from a form. The OnClick event of the button fires a macro to open the report. How can...
2
by: BikeToWork | last post by:
I found code online which uses Windows API's to save Access reports to pdf format files. I'm using the full version of Adobe Acrobat Professional 7.0. The problem is that regardless of what I put...
1
by: seawolf | last post by:
Is there a way to create a numbering for the Details in an Access 2003 Report. For instance, if a report has 12 records that make up the Details in a report, can these be numbered, i.e., 1, 2, 3, 4,...
0
by: cbj0517 | last post by:
Hello: I am trying to create a Pivot table in an Access 2003 report. I have SQL that retunrs 6 columns of data, (month_year, units, products_per_unit, gross_sales, price_pe_product, and...
1
by: klinke1006 | last post by:
Hi, I have written several Access 2003 reports. These reports run properly for some of the users of the Access Application. For others a report that should display on one page displays on many...
5
by: lionelm2007 | last post by:
When I view a report in another machine, it looks slightly different. First of all, the logo in the report heading does not show up when you print, also in print preview, you can see the last letter...
1
by: J360 | last post by:
In Access 2003, I'm creating a dynamic report and am trying to place text boxes into group headers. One report will have 6 group levels. I can place controls in the first two group headers easily...
1
by: lmyers | last post by:
I am a novice MS Access user. I have created a simple purchase order DB with a simple report that displays the contents of individual POs as they are created. My issue is that I can navigate to...
2
by: passterelli | last post by:
I have created a report that displays the contents of large memo fields. The report must be exported to MS Word (or .rtf) so the type style and type size can be modified according to the final user's...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.