Use custom function filtered information

[crazyhouse]

I am using a custom fuction (I got the information from Microsofts site for the code)

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

I am actually deriving the median from the table and field with an unbound text box with the following control source.

=Median("<Table Name>", "<FieldName >")

My problem is that within my db there are only 2 fields, [gdate] and [reading]

gdate = date the reading was taken
reading = actual reading taken

I need the database to derive the median from the month the reading was taken without considereing the day or year that the reading was taken (i have readings from the last 120 years)

When i filter the data (by right clicking and choosing one of the automatic date filters to filter by month) the median doesnt change because it is choosing the median from the entire table.

Does anybody know how to get the median () fuction to work on the filtered data, and not the whole table. If i have to i know that i could just create 12 tables, but i was hoping not to do that.

[ADezii]

I'm a little confused as to exactly what you are requesting - kindly post some sample data, along with what the desired results should be.

[NeoPa]

On your form, is there a current record showing, from whose date (month) you want the Median function to calculate a result?

[NeoPa]

If so, and I guess there must be, you could pass the date value (by referring to the control on the form where this is shown) to the Median() function.

The Median() function code itself would need to be changed to accept and process the date data such that the relevant result is produced. Let me know if this makes sense or whether more explanation is needed.

PS. Congratulations to ADezii on 4,000 posts. Please visit the Milestones Forum ;)

[ADezii]

If so, and I guess there must be, you could pass the date value (by referring to the control on the form where this is shown) to the Median() function.

The Median() function code itself would need to be changed to accept and process the date data such that the relevant result is produced. Let me know if this makes sense or whether more explanation is needed.

PS. Congratulations to ADezii on 4,000 posts. Please visit the Milestones Forum ;)

Thanks NeoPa, right on your tail!