By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Use custom function filtered information

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

Expand|Select|Wrap|Line Numbers
  1. Function Median (tName As String, fldName As String) As Single
  2.   Dim MedianDB As DAO.Database
  3.   Dim ssMedian As DAO.Recordset
  4.   Dim RCount As Integer, i As Integer, x As Double, y As Double, _
  5.       OffSet As Integer
  6.   Set MedianDB = CurrentDB()
  7.   Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName & _
  8.             "] FROM [" & tName & "] WHERE [" & fldName & _ 
  9.             "] IS NOT NULL ORDER BY [" & fldName  & "];")
  10.   'NOTE: To include nulls when calculating the median value, omit
  11.   'WHERE [" & fldName & "] IS NOT NULL from the example.
  12.   ssMedian.MoveLast
  13.   RCount% = ssMedian.RecordCount
  14.   x = RCount Mod 2
  15.   If x <> 0 Then
  16.      OffSet = ((RCount + 1) / 2) - 2
  17.      For i% = 0 To OffSet
  18.         ssMedian.MovePrevious
  19.      Next i
  20.      Median = ssMedian(fldName)
  21.   Else
  22.      OffSet = (RCount / 2) - 2
  23.      For i = 0 To OffSet
  24.         ssMedian.MovePrevious
  25.      Next i
  26.      x = ssMedian(fldName)
  27.      ssMedian.MovePrevious
  28.      y = ssMedian(fldName)
  29.      Median = (x + y) / 2
  30.   End If
  31.   If Not ssMedian Is Nothing Then
  32.      ssMedian.Close
  33.      Set ssMedian = Nothing
  34.   End If
  35.   Set MedianDB = Nothing
  36. End Function
  37.  
I am actually deriving the median from the table and field with an unbound text box with the following control source.

=Median("<TableName>", "<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.
Oct 5 '08 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,680
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.
Oct 5 '08 #2

NeoPa
Expert Mod 15k+
P: 31,712
On your form, is there a current record showing, from whose date (month) you want the Median function to calculate a result?
Oct 5 '08 #3

NeoPa
Expert Mod 15k+
P: 31,712
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 ;)
Oct 5 '08 #4

ADezii
Expert 5K+
P: 8,680
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!
Oct 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.