median function problem

[Lonnie]

Hey all, noob here---I've been working on this function for several days,
but I'm still getting "Undefinded Function" when running this:

My query field: MEAN: Dmedian("PLASMA PRODUCTION","MINUTES")
(where as Plasma production is the table, minutes is the record set i want to
find the median of)

and my code:

Option Compare Database

Public Function dmedian(Expr As String, Domain As String) As Double

End Function
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Temp As Double
Dim OrderedSQL As String

'construct SQL
OrderedSQL = "SELECT " & Expr
OrderedSQL = OrderedSQL & " FROM " & Domain
If Criteria <> "" Then
OrderedSQL = OrderedSQL & " WHERE " & Criteria
End If
OrderedSQL = OrderedSQL & " ORDER BY " & Expr & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(OrderedSQL)

rs.MoveLast
NumRecords = rs.RecordCount
rs.MoveFirst
rs.Move Int(NumRecords / 2)
Temp = rs.Fields(Expr)

If NumRecords / 2 = Int(NumRecords / 2) Then
'there is an even number of records
rs.MovePrevious
Temp = Temp + rs.Fields(Expr)
dmedian = Temp / 2
Else
'there is an odd number of records
dmedian = Temp
End If

rs.Close
db.Close
End Function

not real sure what I'm missing, enlightenment is appriciated!

 

[Roger Carlson]

First of all, where is the DMedian function? In a general module or in a
form. It should be in a general module. Second, is the DMedian function
the one that you are getting the error on? Make sure you have a reference
to Microsoft DAO 3.6 Object Model in your References (Tools>References).
Lastly, the order of your parameters is reversed. The Domain should be the
table, so should be in the second position.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Median.mdb" which illustrates my solution to this (it's very
similar), but it comes with an article that explains how it works. You
might find that useful. You can find the sample here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=325

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Lonnie]

Roger:
This is a general module. I have debugged and found the end function at the
very top, took it out, but still not working. When I try to reference DAO 3.6,
i get an error "Name conflicts with existing module, project, or object
library".

 

[Roger Carlson]

Funny, I missed the End Function altogether. When you go to your
references, do any of them show as MISSING?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Lonnie]

No sir, nothing show as missing

 

[Lonnie]

as it sets now, absolutely nothing happens when i try to hit "run", or try to
view in any format-- Im assuming that's due to the problems, but it's a
different response....

 

[Roger Carlson]

I'm sorry, but I can't see anything wrong with it. Have you tried
decompiling your database?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Lonnie]

Thank you for trying, I really appriciate it. I am pretty sure the problem is
down to the fact I can't reference the DAO 3.6 library, which I will start
working on now

again, thank you for heping me out