Median average doesn't change with any parameters (1 Viewer)

[tezread]

I have a module which creates a function to generate the median of a field 'ReferraltoTreatment' (measured in days)

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

The function is called in a query

PARAMETERS [Forms]![frmReport]![startdate] DateTime, [Forms]![frmReport]![enddate] DateTime;
SELECT Median("qryEpisode","ReferralToTreatment") AS MedianReferraltoTreatment
FROM qryEpisode
WHERE (((qryEpisode.Date_Referred) Between [Forms]![frmReport]![startdate] And [Forms]![frmReport]![enddate]))
GROUP BY Median("qryEpisode","ReferralToTreatment");


BUT - when you change the start and end dates the median value always stays the same.

 

[CBrighton]

This is the recordset you are using the median function on:

Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")

As the start & end dates in your query are not part of this recordset the dates you enter will have no effect on this function.

Best thing to do would edit the function to allow 2 dates to be passed to it as well as the 2 strings it currently accepts and then pass your date fields to it in the query.

:edit:

2 options I guess, add 2 optional date variables and an optional date field variable.

The other option is hard-code the date field name into the SQL, but your current function looks like it's designed to work on any table so I'd advise against that.

 

[tezread]

I think I can add 2 optional date variables but I need to reference the form text boxes where the start and end date are

i.e. Between [Forms]![frmReport]![startdate] And [Forms]![frmReport]![enddate]))

 

[tezread]

not sure how to code this though

 

[vbaInet]

In case you have forgotten, please enclose your code in code tags. It makes it more readable.

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

Where is your query being displayed? On a form? You need to requery the form in the After Update event of both textboxes.

 

[tezread]

In case you have forgotten, please enclose your code in code tags. It makes it more readable.

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

Where is your query being displayed? On a form? You need to requery the form in the After Update event of both textboxes.

Code tagged - my apologies

This is a query that creates a report.
I know what I need to do but don;t know how to do it lol

 

[vbaInet]

So when you enter a new date in the textbox(es) do you re-open the report? You need to.

 

[tezread]

So when you enter a new date in the textbox(es) do you re-open the report? You need to.

yes the report reopens at the moment

I am not sure how to translate this SQL in AfterUpdate()

WHERE (((qryEpisode.Date_Referred) Between [Forms]![frmReport]![startdate] And [Forms]![frmReport]![enddate]))

into VBA though

 

[vbaInet]

Do you want to change the code completely?

 

[tezread]

not really. There are about 6 reports which works fine as it is but for the report that calculates median averages

 

[vbaInet]

I mean are you happy to change the median function permantly? Or are other reports using that function?

 

[tezread]

I mean are you happy to change the median function permantly? Or are other reports using that function?

ah right - the median function applies to just one report so I am happy for that to change yes. sorry

 

[vbaInet]

Here's the amended part:

Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & "] " & _
                      "FROM [" & tName & "] " & _
                      "WHERE [" & fldName & "] IS NOT NULL " & _
                           "AND (Date_Referred  BETWEEN " & [Forms]![frmReport]![startdate] & " AND " & _
                                               [Forms]![frmReport]![enddate] & ") " & _
                      "ORDER BY [" & fldName & "];")

 

[tezread]

Morning - hope you had a good weekend.

Picked this up again and have tried

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 " & _
                           "AND (Date_Referred  BETWEEN " & [Forms]![frmReport]![startdate] & " AND " & _
                                               [Forms]![frmReport]![enddate] & ") " & _
                      "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

but I get a en error message run time error 3021 'no current record'

 

[CBrighton]

Firstly, did the 2 form controls hold valid dates where you are 100% sure there should have been records?

Secondly there's no #'s around the dates, try adding them:

Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & "] " & _
                      "FROM [" & tName & "] " & _
                      "WHERE [" & fldName & "] IS NOT NULL " & _
                           "AND (Date_Referred  BETWEEN [B]#[/B]" & [Forms]![frmReport]![startdate] & "[B]#[/B] AND [B]#[/B]" & _
                                               [Forms]![frmReport]![enddate] & "[B]#[/B]) " & _
                      "ORDER BY [" & fldName & "];")

 

[vbaInet]

It would be useful to know what code line the error occured??

 

[tezread]

It would be useful to know what code line the error occured??

ah yes

x = ssMedian(fldName)

 

[CBrighton]

If it's a lack of records I would guess that "ssMedian.MoveLast" caused the error (though I agree confirmation would be useful).

You should always check for .EOF before trying to use a recordset imo. If the recordset is at the end of file as soon as it's opened then there are no records.

 

[CBrighton]

In that case it's finding records, but the MovePrevious's in the function are moving it back past the first record.

 

[vbaInet]

In your other thread (about a week ago), I pointed you to a thread containing a Median function written by me that works. It will be pointless trying to debug someone else's code.