I am using this module in my access database for median : - Public Function DMedian( _
-
ByVal strField As String, ByVal strDomain As String, _
-
Optional ByVal strCriteria As String) As Variant
-
-
' Purpose:
-
' To calculate the median value
-
' for a field in a table or query.
-
' In:
-
' strField: the field
-
' strDomain: the table or query
-
' strCriteria: an optional WHERE clause to
-
' apply to the table or query
-
' Out:
-
' Return value: the median, if successful;
-
' Otherwise, an Error value.
-
-
Dim db As DAO.Database
-
Dim rstDomain As DAO.Recordset
-
Dim strSQL As String
-
Dim varMedian As Variant
-
Dim intFieldType As Integer
-
Dim intRecords As Integer
-
-
Const errAppTypeError = 3169
-
-
On Error GoTo HandleErr
-
-
Set db = CurrentDb()
-
-
' Initialize return value
-
varMedian = Null
-
-
' Build SQL string for recordset
-
strSQL = "SELECT " & strField & " FROM " & strDomain
-
-
' Only use a WHERE clause if one is passed in
-
If Len(strCriteria) > 0 Then
-
strSQL = strSQL & " WHERE " & strCriteria
-
End If
-
-
strSQL = strSQL & " ORDER BY " & strField
-
-
Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)
-
-
' Check the data type of the median field
-
intFieldType = rstDomain.Fields(strField).Type
-
Select Case intFieldType
-
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
-
' Numeric field
-
If Not rstDomain.EOF Then
-
rstDomain.MoveLast
-
intRecords = rstDomain.RecordCount
-
' Start from the first record
-
rstDomain.MoveFirst
-
-
If (intRecords Mod 2) = 0 Then
-
' Even number of records
-
' No middle record, so move to the
-
' record right before the middle
-
rstDomain.Move ((intRecords \ 2) - 1)
-
varMedian = rstDomain.Fields(strField)
-
' Now move to the next record, the
-
' one right after the middle
-
rstDomain.MoveNext
-
' And average the two values
-
varMedian = (varMedian + rstDomain.Fields(strField)) / 2
-
' Make sure you return a date, even when
-
' averaging two dates
-
If intFieldType = dbDate And Not IsNull(varMedian) Then
-
varMedian = CDate(varMedian)
-
End If
-
Else
-
' Odd number or records
-
' Move to the middle record and return its value
-
rstDomain.Move ((intRecords \ 2))
-
varMedian = rstDomain.Fields(strField)
-
End If
-
Else
-
' No records; return Null
-
varMedian = Null
-
End If
-
Case Else
-
' Non-numeric field; so raise an app error
-
Err.Raise errAppTypeError
-
End Select
-
-
DMedian = varMedian
-
-
ExitHere:
-
On Error Resume Next
-
rstDomain.Close
-
Set rstDomain = Nothing
-
Exit Function
-
-
HandleErr:
-
' Return an error value
-
DMedian = CVErr(Err.Number)
-
Resume ExitHere
-
End Function
Rather than finding text strings on the form/report, I have set up an expression in the control source. Where the field in question is SalePrice and the table in question is GENERAL. - =DMedian("SoldPrice","GENERAL")
However it returns "#Error" in the control.
Any Ideas on what is going wrong?
The field in question a currency datatype.
11 6364 NeoPa 32,568
Recognized Expert Moderator MVP Wisni1rr:
Where the field in question is SalePrice and the table in question is GENERAL.
- =DMedian("SoldPrice","GENERAL")
I expect it's the wrong field name.
Thank you, NeoPa.
The field in question is SoldPrice from the GENERAL table. The CODE was posted correctly in the first post. - =DMedian("SoldPrice","GENERAL")
The first post was incorrect in regards to the field in question.
NeoPa 32,568
Recognized Expert Moderator MVP
So does that mean it's working now?
No. The error still exists.
I also have the same error when trying with a different field.
I am using the code as an expression in the ControlSource Property. - =DMedian("SoldPrice","GENERAL")
and - =DMedian("DOM","GENERAL")
SoldPrice is a number datatype set as currency and DOM is a calculated field returning a number.
Both read as #ERROR in report view.
NeoPa 32,568
Recognized Expert Moderator MVP
In that case I see nothing immediately likely to cause that, but you've posted a hundred lines of code there so you'll be lucky if someone goes through it all for you very carefully.
Have you considered calling it directly from the Immediate pane and tracing the execution to see where it behaves differently from what you'd expect ( Debugging in VBA)?
I tried to debug and did not come back with any halts.
However, I noticed that I do not have the Microsoft DAO 3.6 Object Library activated under my VBA references. I tried to activate and it and get an error dialogue saying "Name conflicts with existing module, project, or object library." I do not have any other modules in the current database. I also tried to add the reference in a new database and received the same error.
Could this be the culprit?
Rabbit 12,516
Recognized Expert Moderator MVP
Do those fields contain a lot of nulls? I don't remember if the code handled nulls. Also, you said your DOM field is a calculated field... like in a query? But you also said GENERAL is a table. Your calculated query field isn't going to exist in your table.
The current dataset does not contain any nulls. DOM is in the table design view as a calculated datatype.
The recordset for the report comes from a simple select query.
SOLVED!
I used the following code to calculate the median of the fields. Both fields in question have returned appropriate values in my first round of tests. - 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
-
I also needed to enter this into the Declarations
Thanks for your help guys!!!
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ross Contino |
last post by:
Hello to all:
I have been searching the web for examples on how to determine a median
value in a mySQL table. I have reviewed the article at
http://mysql.progen.com.tr/doc/en/Group_by_functions.html. I am an
experienced VB programmer that has recently moved to PHP/mySQL. My employer
has a text file outputted from a vendor specific software with data.
However it cannot be manipulated because it is text. I created a web that
reads the...
|
by: michael way |
last post by:
I read the follow query about calculating median posted by Daivd Porta
on 10/8/03.
CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOT
NULL)
INSERT INTO SomeValues VALUES ('A',1)
INSERT INTO SomeValues VALUES ('B',2)
INSERT INTO SomeValues VALUES ('C',3)
INSERT INTO SomeValues VALUES ('D',4)
|
by: nick.vitone |
last post by:
Hi,
I'm somewhat of a novice at Access, and I have no experience
programming whatsoever. I'm attempting to calculate the statistical
median in a query. I need to "Group by" one column and find the median
of the another, though I'm not sure how. I've been able to add the
"Median" function (from the Microsoft Access Help Archive), but I can't
figure out how to incorporate that into the Totals. Do I need to use
the "expression" and...
|
by: jimfortune |
last post by:
In
http://groups-beta.google.com/group/comp.databases.ms-access/msg/46197725b88fc3fd?hl=en
I said:
If qryRankForMedian is changed to select only values within a group
(along with a suitable WHERE clause for WantRanking) it may be possible
to get the median of each group in its output line as well by using SQL
to reference the Groups value. To do that for the Median function
|
by: Morgan Cheng |
last post by:
Hi All,
I was taught that argument valuse is not supposed to be changed in
function body. Say, below code is not good.
void foo1(int x)
{
x ++;
printf("x+1 = %d\n", x);
}
It should be "refactor-ed" to be
| |
by: uspensky |
last post by:
I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
|
by: jonm4102 |
last post by:
I'm trying to calculate the median of some numerical data. The data can
only be found in a query (henceforth query 1) field I previously made,
and I would prefer to calculate the median in a new query it without
making a table out of query 1. I can't find a median function in the
"Total" field, so is there so way to make an expression to calculate
the median of the orignial data from query 1 in my new query?
Also, what does name by...
|
by: Jaime Leivers |
last post by:
Here's a median function in access that you can call in any query. You
could change this to any excel function you wanted. Most people can
find the windows help file that says how to call an excel function but
don't know how to pass an array of the recordset they made into that
function. This uses GetRows that nicely creates an array that can be
passed into excel.
I have this data table for testing. The table name is TestData
...
|
by: rrstudio2 |
last post by:
I am using the following vba code to calculate the median of a table
in MS Access:
Public Function MedianOfRst(RstName As String, fldName As String) As
Double
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
|
by: MrDeej |
last post by:
Hello!
I have a table wich contains product number and different time date attachet pr product number.
Today we have used to calculate MEAN to get an average time consumed pr product. We have now learned that MEDIAN would give us more quality data since we sometimes have abnormalities on time consumed.
However. Access 2007 does not seem to have a MEDIAN function, and when i google it i only find code to do this for an entire dataset.
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |