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

Error with Median Code

P: 78
Hi there,

I have a VBA code that I use to compute the median:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5.  
  6. Function DMedian(tName As String, fldName As String) As Single
  7.   Dim MedianDB As DAO.Database
  8.   Dim ssMedian As DAO.Recordset
  9.   Dim RCount As Integer, i As Integer, x As Double, y As Double, _
  10.       OffSet As Integer
  11.   Set MedianDB = CurrentDb()
  12.   Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
  13.             "] FROM [" & tName & "] WHERE [" & fldName & _
  14.             "] IS NOT NULL ORDER BY [" & fldName & "];")
  15.   'NOTE: To include nulls when calculating the median value, omit
  16.   'WHERE [" & fldName & "] IS NOT NULL from the example.
  17.   ssMedian.MoveLast
  18.   RCount% = ssMedian.RecordCount
  19.   x = RCount Mod 2
  20.   If x <> 0 Then
  21.      OffSet = ((RCount + 1) / 2) - 2
  22.      For i% = 0 To OffSet
  23.         ssMedian.MovePrevious
  24.      Next i
  25.      DMedian = ssMedian(fldName)
  26.   Else
  27.      OffSet = (RCount / 2) - 2
  28.      For i = 0 To OffSet
  29.         ssMedian.MovePrevious
  30.      Next i
  31.      x = ssMedian(fldName)
  32.      ssMedian.MovePrevious
  33.      y = ssMedian(fldName)
  34.      DMedian = (x + y) / 2
  35.   End If
  36.   If Not ssMedian Is Nothing Then
  37.      ssMedian.Close
  38.      Set ssMedian = Nothing
  39.   End If
  40.   Set MedianDB = Nothing
  41. End Function
  42.  
However, the function is calculating ALL records in my database rather than using the conditions of my query.

My query takes the data in an unbound textbox(es) [qField] on a form [Search] and passes it on to a report.

At this time all my SQL conditions are in this format:

Expand|Select|Wrap|Line Numbers
  1. Like [Forms]![Search].[qField] & "*"
Can anyone provide insight into why this function is using ALL records in my database rather than my RecordSource which is based on a query? All other data is following the query just not the data using the DMedian() Function.

Thanks in Advance!
Nov 30 '11 #1
Share this Question
Share on Google+
10 Replies


dsatino
100+
P: 393
It looks to me like your function is doing exactly what you are asking it to do. The only filter on your function recordset is the 'is not null'.

I think you need to add a filter field to your function to make if work as you intend.
ie.
Expand|Select|Wrap|Line Numbers
  1. Function DMedian(tName As String, fldName As String,fldfilter as string) As Single 
  2.  
then add that fldfilter string to your where statement.
Essentially mimic the prebuilt domain funtions.
Nov 30 '11 #2

P: 78
dsatino,

That sounds like a practical solution. I am trying to have this function act like the buit in AVG and such functions.

However I'm a beginner to VBA. How would I add this to my function?

The example in your last post only shows the first line of code.

Thank you.
Nov 30 '11 #3

dsatino
100+
P: 393
I think you're going to find that your second posting of this question will get deleted (Duplicating posts is generally frowned upon) which is why I'm responding to this one.

In any case, just change the first line to what I gave you and then modify the "WHERE" statement of the SQL string in the function. Actually, make the first line this:

Expand|Select|Wrap|Line Numbers
  1. Function DMedian(tName As String, fldName As String,optional fldfilter as string) As Single
  2.  
Now for the part that you are missing from a conceptual standpoint.

Apparently you're trying to make a function that you can add to a query and return the results, but what you've actually created (or copied from elsewhere I'd guess) is a 'domain' function which returns a single result for an entire domain. Try running your query with the Avg() function on your field and the DAvg([field],[table]) on the same field. The Avg will return what you're looking for but the Davg() will be the same on every line.

Your assumption is that by calling function on aggregated line in your query, Access will automatically filter your 'domain' function based on your query filters, but that's simply not the case. When you use a 'domain' function in a query, you are essentially running a new query on every line of aggregation. So in order to make this work, you need to pass a parameter to your function for every single field in your query that appears in the "GROUP BY" line.
Dec 2 '11 #4

P: 78
I won't have a repost issue happen again. Thank you for the warning.

As you have speculated, I'm trying to develop this function to work in the same respect as the Avg() rather than the DAvg().

I don't understand what I would "GROUP BY". My function is called in an unbound textbox control that is in the header section of my report. The report is generated by a query.
Dec 13 '11 #5

dsatino
100+
P: 393
is there a 'GROUP BY' clause in the query that your report is based on?
Dec 16 '11 #6

P: 78
No sir. I've been discussing it with another forum. We were thinking to call the field from the query rather than the table itself. However it is presenting a parameter issue. They suggested dynamically populating a query with the parameter already saved so that it doesn't attempt to ask for it when you run this function. I'm not sure how to do that exactly.
Dec 16 '11 #7

dsatino
100+
P: 393
What's the name of the field that you are using the "Like Forms..." sql condition on?
Dec 16 '11 #8

P: 78
There are several fields. The fields in question may be "City" or "PropertyType" for example. On my search forms all the textboxes are named the same as the field with the addition of a "q" in front of them such as "qCity" and "qPropertyType"
Dec 16 '11 #9

dsatino
100+
P: 393
Ok, I think i was overthinking/missing what you were doing.

It occurred to me that the original problem you were having was that you were getting the median for the entire table... which means you were passing the the table name to function for the 'tname' variable.

If you're report is based on a named query, then all you need to do is pass the query name and query field name in place of where you were originally passing in the table name and table field
Dec 17 '11 #10

P: 78
dsatino,

Thank you for your help. You are correct in suggesting to call the query rather than the table in the VBA. I have discovered that the code is working properly and was creating errors due to the involvement of the parameters involved in my query. I have decided to rework my query to reflect this discovery. Thank you for leading me on the correct path to solving my problem!
Dec 20 '11 #11

Post your reply

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