Error with Median Code

[wisni1rr]

Hi there,

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

Option Compare Database
Option Explicit



Function DMedian(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
     DMedian = ssMedian(fldName)
  Else
     OffSet = (RCount / 2) - 2
     For i = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     x = ssMedian(fldName)
     ssMedian.MovePrevious
     y = ssMedian(fldName)
     DMedian = (x + y) / 2
  End If
  If Not ssMedian Is Nothing Then
     ssMedian.Close
     Set ssMedian = Nothing
  End If
  Set MedianDB = Nothing
End Function

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:

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!

[dsatino]

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.

Function DMedian(tName As String, fldName As String,fldfilter as string) As Single

then add that fldfilter string to your where statement.
Essentially mimic the prebuilt domain funtions.

[wisni1rr]

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.

[dsatino]

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:

Function DMedian(tName As String, fldName As String,optional fldfilter as string) As Single

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.

[wisni1rr]

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.

[dsatino]

is there a 'GROUP BY' clause in the query that your report is based on?

[wisni1rr]

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.

[dsatino]

What's the name of the field that you are using the "Like Forms..." sql condition on?

[wisni1rr]

There are several fields. The fields in question may be "City" or "PropertyTy pe" 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 "qPropertyT ype"

[dsatino]

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

[wisni1rr]

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!