Finding a Median

[Mobiusrune]

I have a report that calculates a average value from a field. Is there a way to figure out the median value? I did not see a function for this.

 

[Mihail]

If it is not, you can design one.
Maybe this article will help you.
Cheers.

 

[Mobiusrune]

Okay...(Nurse here and not IT professional)...Not to be dense, but the article states that to use this code I need to "Use the References command in the Tools menu to add the necessary reference". I did import the module just fine.

Where is the References Command and is it supposed to be in the Database Tools Ribbon? Don't mean to be a pest, but I am a little lost with this. Help?!?!?!?

 

[Mihail]

VBA editor, Tools menu.

 

[Mobiusrune]

Do I only need to select the MS DAO Object Library? Is that what I am looking for?

 

[Mihail]

Try and you will know if this is ok

 

[Mobiusrune]

I didn't get the DAO error after selecting the DAO Library. But, I am still getting the error on my report. I have the expression written as follows:

=DMedian("Expr1","qryDataAnalysisCenter")

Does this look right? The article states that the last part is optional.

 

[Mihail]

I never read that article . But now I see this function:
Public Function DMedian( _ ByVal strField As String, ByVal strDomain As String, _ Optional ByVal strCriteria As String) As Variant

And this say to me that the involved parameters for this function are:
strField - the name of that field where you have data for calculate the median
strDomain - the name of the table (or query) where the strField is

Optional you can use strCriteria in order to filter the values in that table/query.

So, your Expr1 should be the name for the field in qryDataAnalysisCenter

=DMedian("FieldName","qryDataAnalysisCenter")

 

[Mobiusrune]

Expr1 is the Field name. It is a calculated field in a qry based off of other fields from a table. Expr 1 is something like this:

IIF([Time1]>[Time2],DateDiff("n",[Time1],[Time2])+1440,DateDiff("n",[Time1],[Time2]))

Access automatically calls this field in the query Expr1. Expr1 is displayed in a report. I was able to calculate Avg([Expr1) and the StDev([Expr1]) without issue, but DMedian does not seem to like "Expr1" as a field name...

So stuck I will be for the time being.
Mihail thank you so much for your time and patience.

 

[Mihail]

If you upload the database me (or others) can take a look and trying to understand what happen.

 

[Mobiusrune]

It won't let me. Upload only allows for 2MB and this is 5.3.

 

[JHB]

Have you zipped it + a "Compact and Repair" before?

 

[Mobiusrune]

No, because it is late and my brain isn't working...here it is...

 

[Mobiusrune]

The issues is getting the Median values for the Expr# fields in the two reports. PtMRN field is blank r/t Protected Health Information.

 

[Mihail]

And is no need for all records if you have a huge database.
And do a Compact and Repair before uploading (or zipped).

 

[Mobiusrune]

Okay...compacted and repaired...and zipped.

This is what happens when you throw a nurse into IT...but I am having fun with this...

 

[Mihail]

First error:
In Command101_Click() your stDocName point to the query not to the report.
Change the name then let us know if this solve the error.

 

[Mobiusrune]

Nope. Does not fix it.

 

[Mihail]

The row source for text132 in the report try to calculate the median using the report.
Change this
=DMedian("Expr1";"rptDataAnalysisCenter")
to
=DMedian("Expr1";"qryDataAnalysisCenter")

Hope you have keep a copy for your original database

Inform about the error.

 

[Mobiusrune]

Yes I have an original copy (and a backup).

For Text 132 I have tried it both ways and when I run the report the output is #Error. :banghead: