Help with Calculating MEDIAN !!!

[collyrium]

I have scoured the web and found several seemingly useful modules to calculate the median of a set of records, but I can't get any of them to work.

The latest one I tried I found here (http://support.microsoft.com/default.aspx?scid=kb;en-us;q210581)

I followed the directions to the T, but when I call the function in a form like so:
=Median([qryMilestoneTest],[TAtoAuditDraftRcvd])

It doesn't work. Instead when I preview the form I am prompted to Enter Parameter Value twice, once for Median and once for qryMilestoneTest.

What am I doing wrong? Anybody, please help!!

 

[pono1]

After a glance, it looks like the article calls for this in a control:

=Median("<TableName>", "<FieldName>")

Notice the quote marks around the table name and field name. So my best guess:

=Median("qryMilestoneTest","TAtoAuditDraftRcvd")

Regards,
Tim

 

[collyrium]

Thanks Tim.

I 've tried that. But it still prompts me to "Enter a Parament Value".

I feel like I'm missing something obvious. But I just have no idea what I'm missing.

 

[pono1]

C,

It's a masochistic endeavor, writing, reading and debugging code --- even when you're copying and pasting large chunks from other sources.

Take a breath, knit your brow, and try this:

. Create a new, clean Access file and import relevant tables from your working file.
. Create a new form in Design View.
. Add an unbound textbox to the form.
. Go to the code window (Alt-F11): click Insert-Module if the screen is gray.
. Paste in the code from the article. (Be sure to paste -- do not re-type it)
. Make sure DAO 3.6 is installed in the References Library.
. Back to the form, in the textbox's control source property paste in the formula from the article and then type in your table and fieldname: =Median("TableName", "FieldName").
. TEST by opening the form...
. If that works, then re-create your query and try using it's name rather than a table name in the formula.
. If that works, you know you can make it work (at least in a clean file), so you now need to compare the two files: the test file versus the working file and find differences between them.

This may or may not make you feel any better: I created a quick test file in Access 2000 and the code from the article worked when using either a table and a query. This suggests you are, as you say, missing something obvious -- a transposed character, wrong table or query or field name, misplaced code -- or you are presuming that something works in a certain manner when, in fact, it doesn't.

Give it another shot, checking yourself along the way...

Regards,
Tim

 

[collyrium]

Ugh!!

Tim,

Thanks for your very helpful suggestion. I tried it with dummy data, very simple stuff, but it still doesn't work. What am I doing wrong?

Here's my file. http://a.parsons.edu/data/db1.zip

Thanks again.

 

[collyrium]

oops!

It should read

http://a.parsons.edu/~torres/data/db1.zip

 

[collyrium]

Got It!!!

Thanks for all your help Tim.

Cheers.

 

[RichMas]

Hi,

I have followed your postings as I have an identical problem.

I have managed to return the median value for a field in a table:

"Function median(tblmedian As String, salary As String) As Single"

But I need to be able to do this from a field in a query?!?!?

I have attempted to run with:

"Function median(qrymedian As String, salary As String) As Single"

But I receive an error message:
Run time error 3061:
Too few parameters. Expected 1.

Please help

Thanks
Richard

 

[pono1]

Richard,

Once you paste the function into your file from the web page, do not change it.

What you do change is the formula that's put into the control source property of your controls -- this formula calls the median function.

To use a table as your data source:
=Median("TableName", "FieldName"), replacing TableName with your table name and Fieldname with your field name.

To use a query as your data source:
=Median("QueryName", "FieldName"), replacing QueryName with your query name and Fieldname with your field name.

Regards,
Tim

 

[RichMas]

Thanks Tim
All works fine...

Richard