Re: How do I retrieve the median value of a field?
From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 11/01/04
- Next message: John Spencer (MVP): "Re: How do I retrieve the median value of a field?"
- Previous message: alex: "pivot query as recordsource"
- In reply to: MaryW: "Re: How do I retrieve the median value of a field?"
- Next in thread: John Spencer (MVP): "Re: How do I retrieve the median value of a field?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 1 Nov 2004 17:57:55 -0500
You must be using Access 2000 or 2002. Database is a DAO object, and, by
default, Access 2000 and 2002 only have references to ADO.
With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library
If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)
The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "MaryW" <MaryW@discussions.microsoft.com> wrote in message news:FB3641D7-BE3C-425E-9F5F-7DD6ADCF8E40@microsoft.com... > Hi- > Thanks for helping through my first attempt at VBA. I corrected the query > and defined the two arguements as the table and the field names. The NEW > error message that I get is "Compile Error: User-define type is not defined". > In the module, the second line of code is highlighted > Dim MedianDB As Database > > Any more suggestions? > > "John Spencer (MVP)" wrote: > > > Well, assuming that the function actually works (which I won't vouch for since > > it is not my code), did you pass it two strings? One the name of the table, > > and secondly the name of the field you want to generate the median on. > > > > You should have a column something like: > > > > Field: GetMedian: Median("MyTableName","MyFieldName") > > > > Now the problem I see with this entire function is that it basically gets the > > median of the field for ALL the records in the table or a query. In other > > words, it does not get the median of a group or the median of a query that is > > returns a limited set of records using a where clause. > > > > You could still use it by creating a query that returns the desired record and > > then use that query as the basis of what you need to generate the median values. > > > > MaryW wrote: > > > > > > I hope I'm not being dense here, but I'm still having trouble using the > > > median in a query. Now I get the message "Wrong number of arguments used with > > > function in query expression" > > > > > > Please help... > > > > > > "John Spencer (MVP)" wrote: > > > > > > > I think you have a small problem in this function as posted. You have a line > > > > continuation inside the quotes. > > > > > > > > Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _ > > > > "] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _ > > > > NOT NULL ORDER BY [" & fldName$ & "];") > > > > > > > > I would rewrite it for clarity as > > > > > > > > Set ssMedian = MedianDB.Openrecordset ( _ > > > > "SELECT [" & fldName$ & "] " & _ > > > > "FROM [" & tName$ & "] " & _ > > > > "WHERE [" & fldName$ & "] IS NOT NULL " & _ > > > > "ORDER BY [" & fldName$ & "]") > > > > > > > > StCyrM wrote: > > > > > > > > > > Good afternoon > > > > > > > > > > The following function will calculate the Median for you. Simply copy and > > > > > paste this into a new module. > > > > > > > > > > Function Median (tName$, fldName$) As Single > > > > > Dim MedianDB As Database > > > > > Dim ssMedian As Recordset > > > > > Dim RCount%, i%, x%, y%, OffSet% > > > > > 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 > > > > > ssMedian.Close > > > > > MedianDB.Close > > > > > End Function > > > > > > > > > > Best Regards > > > > > > > > > > Maurice St-Cyr > > > > > Micro Systems Consultants, Inc. > > > > > > > > > > >I have a large database in which I'd like to find the median, not average, > > > > > >value of numeric data in several fields. > > > > > > > > > > > >
- Next message: John Spencer (MVP): "Re: How do I retrieve the median value of a field?"
- Previous message: alex: "pivot query as recordsource"
- In reply to: MaryW: "Re: How do I retrieve the median value of a field?"
- Next in thread: John Spencer (MVP): "Re: How do I retrieve the median value of a field?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
