Median average doesn't change with any parameters

[vbaInet]

when the db loads enter andy for username and andy for password
click the auditing button on the home page

I bypassed all that or else I won't have been able to get to your modules and queries. I was on frmReport when I asked.

There's nothing wrong with the function. How do you expect a median when your actual query is doing lots of Avg, Min and Max, whereas the sql statement in the function doesn't have any of that?

 

[tezread]

I bypassed all that or else I won't have been able to get to your modules and queries. I was on frmReport when I asked.

There's nothing wrong with the function. How do you expect a median when your actual query is doing lots of Avg, Min and Max, whereas the sql statement in the function doesn't have any of that?

Hmmm - I tried removing the Avg Min and Max in the query and the wrong result still comes out when choosing the date zone

start 01/01/2011 and end 01/02/2011

and for 01/02/2011 and 10/03/2011

 

[tezread]

Have found a cheeky way around this.

I have created a query called qryRTT which selects the top 50 percent of the Referral to Treatment values, put them in descending order, then created another query which selects the maximum value of qryRTT.

 

[vbaInet]

Alright, your main problem is the format of your date in the query.

Other things to note are:

1. You weren't performing a Median on the correct field because the SQL statement in the function had a fixed field
2. Don't forget the NOT IS NULL criteria you put in SQL statement in the function.
3. The source of the SQL statement should have qryEpisode and not qryRTT because all you're doing is filtering twice.
4. Don't use the function in the query, put it in the Control Source of the textboxes that need to display the Median.

Add these bits:

    Dim firstVal As Double, recCount As Long
    Dim strSQL As String
 
    strSQL = "SELECT [" & strMedianField & "] " & _
             "FROM [qryEpisode] " & _
             "WHERE [ReferralToClinicDate] IS NOT NULL " & _
                 "AND (Date_Referred BETWEEN " & Format([Forms]![frmReport]![startdate], "\#mm\/dd\/yyyy\#") & " AND " & _
                                                 Format([Forms]![frmReport]![enddate], "\#mm\/dd\/yyyy\#") & ") " & _
             "ORDER BY [" & strMedianField & "];"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
    recCount = rs.RecordCount

Everything else remains the same.

Like I mentioned, the code works you just need to understand what your queries are doing and what you want to achieve.

 

[tezread]

thank you vbainet

those changes have made a positive impact

 

[vbaInet]

Glad to hear tezread!