Help finding the Median using MS Access 3

[TJVFree]

I’ve spend the last day researching online how to make access calculate the Median.

I really hoping someone can help me find what I need to do to find the median Number of Loans w/Ovg Pts for each name.

my query is at the bottom.

I have found code, but not know VBA I don’t know how it would work

Any help would be greatly appreciated

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

Back to the top
How to Use the Median() Function
Create a form and add a text box control where you want to display the median values of a data set. Set the ControlSource property of the text box control to the following:
=Median("<TableName>", "<FieldName>")


The value of this control is the median of the data set. Another way to use this function is to call it from within another function that compares the median from different data sets. For example:

 Function CompareMedians()
      Dim MyDB as Database
      .
      .
      .
      X = Median("<TableName>", "<FieldName>")
      Y = Median("<TableName>", "<FieldName>")
      If X > Y Then Debug.Print "The median for X is greatest."
   End Function

SELECT Table1.Type, Table1.BRANCH, Table1.Name, Table1.[Closed Loans], Table1.[Number of Loans w/Ovg Pts], Table1.[Number of Loans w/Undg Pts], Table1.[Percentage w/Ovg Pts], Table1.[Percentage w/Undg Pts], Table1.[Mean_of_w/Ovg Pts], Table1.[Mean_of_w/Undg Pts], Table1.[Median_of_w/Ovg Pts], Table1.[Median_of_w/Undg Pts]
FROM Table1
GROUP BY Table1.Type, Table1.BRANCH, Table1.Name, Table1.[Closed Loans], Table1.[Number of Loans w/Ovg Pts], Table1.[Number of Loans w/Undg Pts], Table1.[Percentage w/Ovg Pts], Table1.[Percentage w/Undg Pts], Table1.[Mean_of_w/Ovg Pts], Table1.[Mean_of_w/Undg Pts], Table1.[Median_of_w/Ovg Pts], Table1.[Median_of_w/Undg Pts];

 

[MajP]

I am not sure what your question is.

When you call this function you pass it a table name and field name and it opens that table or query sorted on your field. This table or query could be any table or query. It is not like an aggregate function used in sql.

I think your question is that you do not want it to return the median of all records in the field, but the median for all records for a given "name". As an aside please tell me you do not have a field named "name". That is a reserved word and can cause a lot of problems. Simple rule is never name anything that could be the same name as some vba or access object (i.e. do not call things "field", "table", "name", "caption", "label", "database", etc.)

so you could modify this function to pass in a "where statement". I am going to be lazy and only handle a string value. But if you want you can modify the code to handle other datatypes.

BUt to call this from a query where I want the median of a field for each "Name". Out of principle I will pretend that the field is not called "name" but "loanName". Also the field I want the median for each is called "w_Ovg_Points".

To call the function from a query
select loanName, someField, ... Median("table1","w_Ovg_Points","loanName = ",loanName),...

The sql code in the function should resolve to something like the following based on what was passed in.

Select w_ovg_Points from table1 where W_ovg_Points IS NOT NULL AND loanName = 'LoanOne' ORDER BY W_Ovg_Points

BTW do not put spaces into names of fields or objects. That is a self inflicted wound.

Function Median (tName As String, fldName As String,[b]strCriteria as string, strValue as string[/b]) As Single
 ....
  strValue = "'" & strValue & "'"
  Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _ 
            "] IS NOT NULL [b] AND " & strCriteria & strValue & "[/b] ORDER BY [" & fldName  & "];")

 

[strongm]

It's pretty much posib;e to write a SQL query that returns the median - but, as MajP says, what exactly is your question?

 

[TJVFree]

MajP,

Thank you so much for helping me understand a little bit better on how to call this function. Sorry for not pointing out that the names in the database were fake but nevertheless I shouldn’t have used them.

After I pasted the code in a Modules and putting the formula below in a query, I’m still not able to get it to run. What am I doing wrong?

Median("MyTestTable","w_Ovg_Points","LoanName = ",LoanName)

Thanks again

from my database

SELECT MyTestTable.Type, MyTestTable.BRANCH, MyTestTable.LoanName, MyTestTable.[Closed Loans], MyTestTable.[Number of Loans w/Ovg Pts], MyTestTable.[Number of Loans w/Undg Pts], MyTestTable.[Percentage w/Ovg Pts], MyTestTable.[Percentage w/Undg Pts], MyTestTable.[Mean_of_w/Ovg Pts], MyTestTable.[Mean_of_w/Undg Pts], MyTestTable.[Median_of_w/Ovg Pts], MyTestTable.[Median_of_w/Undg Pts]
FROM MyTestTable
GROUP BY MyTestTable.Type, MyTestTable.BRANCH, MyTestTable.LoanName, MyTestTable.[Closed Loans], MyTestTable.[Number of Loans w/Ovg Pts], MyTestTable.[Number of Loans w/Undg Pts], MyTestTable.[Percentage w/Ovg Pts], MyTestTable.[Percentage w/Undg Pts], MyTestTable.[Mean_of_w/Ovg Pts], MyTestTable.[Mean_of_w/Undg Pts], MyTestTable.[Median_of_w/Ovg Pts], MyTestTable.[Median_of_w/Undg Pts];

 

[MajP]

I revised the function to make it more understandable and similar to a domain aggregate function

If I want to call this from a query where I want the median unit price for each orderID (using Nwind)

You can try this in the northwind database

SELECT 
 [Order Details].OrderID, 
 [Order Details].UnitPrice, 
 DMedian("Unit Price","Order Details","OrderID = " & [OrderID]) AS MedianUnitPrice
FROM 
 [Order Details]
ORDER BY 
 [Order Details].OrderID, 
 [Order Details].UnitPrice;

I can pass in a criteria or not.

Public Function DMedian(Expression As String, Domain As String, Optional Criteria As String) As Variant
  On Error GoTo errlbl
  Dim rs As DAO.Recordset
  Dim N As Long  'Number of records
  Dim isEven As Boolean 'Is the number of records even
  Dim middleLocation As Long 'If N is odd single location
  Dim leftMiddleLocation As Long 'If N is even there is a rt and left middle
  Dim rightMiddleLocation As Long 'If N is even there is a rt and left middle
  Dim leftMiddleValue As Variant
  Dim rightMiddleValue As Variant
  Dim strWhere As String
  Dim strOrderBy As String
  Dim strSql As String
  
  Expression = "[" & Expression & "]"
  Domain = "[" & Domain & "]"
  
  strSql = "SELECT " & Expression & " FROM " & Domain
  strSql = strSql & " WHERE NOT " & Expression & " IS NULL"
  If Criteria <> "" Then
    strSql = strSql & " AND " & Criteria
  End If
  strSql = strSql & " ORDER BY " & Expression
  'Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
 
  If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    rs.MoveLast
    N = rs.RecordCount
    isEven = ((N Mod 2) = 0)
    If N = 1 Then
      DMedian = rs.Fields(Expression)
    ElseIf isEven Then
      leftMiddleLocation = N / 2
      rightMiddleLocation = leftMiddleLocation + 1
      rs.AbsolutePosition = leftMiddleLocation - 1  'Abs Pos is 0 based
      leftMiddleValue = rs.Fields(Expression)
      'Debug.Print "left mid " & leftMiddleValue
      rs.AbsolutePosition = rightMiddleLocation - 1 'Abs is 0 based
      rightMiddleValue = rs.Fields(Expression)
      'Debug.Print "rt mid " & rightMiddleValue
      DMedian = (leftMiddleValue + rightMiddleValue) / 2
    Else
      middleLocation = (N + 1) / 2
      'Debug.Print "mid Loc " & middleLocation
      rs.AbsolutePosition = middleLocation - 1 '0 based
      'Debug.Print "abs " & rs.AbsolutePosition
      DMedian = rs.Fields(Expression)
    End If
  End If
  Exit Function
errlbl:
  If Err.Number = 3061 Then
    Debug.Print "This usually indicates problems in the sql string. Check all field and table names." & vbCrLf & strSql, vbInformation, "SQL String"
  Else
    Debug.Print Err.Number & " " & Err.Description & vbCrLf & strSql
  End If
End Function

data

Order ID    Unit Price MedianUnitPrice
10248       $9.80      14
10248       $14.00     14
10248       $34.80     14
10249       $18.60     30.5
10249       $42.40     30.5
10250       $7.70      16.8
10250       $16.80     16.8
10250       $42.40    16.8

One thing I need to figure out. I would like to show the error messages in a message box, but if I do that and you run this in a query with 1000s of records you get an error message on each record and you can not get out. I am not sure how I would handle it to show the error only once. If you do not get results check the immediate window for the errors. I left lots of debugs to see how this works.

 

[TJVFree]

Thanks MajP - This is the first debug I got, what do I need to do to fix it?

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

 

[MajP]

That function is junk. Use what I provided.

 

[MajP]

http://www.4shared.com/document/Fv97XClb/MedianDB.html

Here is the demo as I described. Look at the query. It takes the median of a subgroup of records based on a criteria.

 

[TJVFree]

I’m not getting any results and in the immediate window in VBA it shows this:

SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11070 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11070 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11070 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11070 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11071 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11071 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11073 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11073 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11073 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11074 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11075 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11075 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11075 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11076 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11076 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11076 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11076 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11076 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11077 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11075 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11075 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11076 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11076 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11073 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11074 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11075 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11075 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11073 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11073 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11071 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11072 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11070 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11070 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11071 ORDER BY [Unit Price]              64           SQL String
This usually indicates problems in the sql string. Check all field and table names.
SELECT [Unit Price] FROM [Order Details] WHERE NOT [Unit Price] IS NULL AND OrderID = 11071 ORDER BY [Unit Price]              64           SQL String

 

[MajP]

Oops sorry. It appears somehow I sent a bad version. Try this

http://www.4shared.com/document/0FvJVtLW/MedianDB_V20.html

It actually is working as designed, but I was previously testing what happens if you send bad field/table names. The field is "UnitPrice" not "Unit Price" and it appears that I passed "Unit Price"

SELECT [Unit Price]

With that said, I still cannot figure out how the bad file was sent.

 

[TJVFree]

Getting so close.

invalid use of null message?

 

[MajP]

I do not know what to say, and not sure how it is possible. I double checked by downloading it off the link. I opened the query and see the medians no problem. I have no idea why it runs on my computer and not yours. Comment out the error trap and find where in the code it breaks.

 

[strongm]

Just for fun, here is my reworking of MajP's domain aggregate, which calculates the median in the query itself:

[blue]Public Function DMedian(strFieldname As String, strTable As String, Optional Criteria As String) As Variant
    Dim strSelect As String
    Dim strSQL As String

    strSelect = "SELECT TOP 50 PERCENT [" & strFieldname & "] FROM [" & strTable & "] WHERE NOT [" & strFieldname & "] IS NULL " & IIf(Criteria <> "", "AND " & Criteria, "") & " ORDER BY [" & strFieldname & "]"
    strSQL = "SELECT (Max(x." & strFieldname & ")+Min(y." & strFieldname & "))/2 AS Median FROM (" & strSelect & ") AS x, (" & strSelect & " DESC) AS y "

    DMedian = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot).Fields("Median").Value
End Function[/blue]

 

[MajP]

Much smarter and more efficient.

 

[MajP]

This is basically the same as Strongm's suggestion. It has a few more variables so you can
kind of see how it works. By viewing the debug statements you can see how the sql is built.

Public Function DMedian(Expression As String, Domain As String, Optional Criteria As String) As Variant
  On Error GoTo errlbl
 
  Dim strSQL As String
  Dim strSQLx As String
  Dim strSQly As String
  
  Expression = "[" & Expression & "]"
  Domain = "[" & Domain & "]"
  
  strSQLx = "SELECT TOP 50 PERCENT " & Expression & " FROM " & Domain
  strSQLx = strSQLx & " WHERE NOT " & Expression & " IS NULL"
  If Criteria <> "" Then
    strSQLx = strSQLx & " AND " & Criteria
  End If
  strSQLx = strSQLx & " ORDER BY " & Expression
  strSQly = strSQLx & " DESC"
  strSQLx = "(" & strSQLx & ")"
  strSQly = "(" & strSQly & ")"
 ' Debug.Print strSQLx
 ' Debug.Print strSQly
  strSQL = "SELECT (Max(X." & Expression & ")+Min(Y." & Expression & "))/2 AS Median FROM " & strSQLx & " AS X, " & strSQly & " AS y "
 ' Debug.Print strSQL
  DMedian = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)!Median
    Exit Function
errlbl:
  If Err.Number = 3061 Then
    Debug.Print "This usually indicates problems in the sql string. Check all field and table names." & vbCrLf & strSQLx, vbInformation, "SQL String"
  Else
    Debug.Print Err.Number & " " & Err.Description & vbCrLf & strSQLx
  End If
End Function

 

[TJVFree]

I’m sorry for not figuring this out and having to come back and ask for more help. From what I understand I have all the VBA script and formulas I need to find the Median.

If someone could please help me understand what I am missing.

I pasted the VBA code in a Module.

Then all I need to do next is call it. What am I doing wrong?

 

[MajP]

Using the median on this set of data makes little sense because it is already aggregated data.
1)The function is called DMedian not Median as the error message states
2)The third parameter is the criteria
if numeric field "loanName = " & [loanName]
if a text field "loanName = '" & [loanName] & "'"

3)In the query designer you would type something like
SomeNameMedian("FieldName","TableName","OtherFieldName = '" & [OtherFieldName] & "'")