Access Cookbook - Ken Getz [126]
Figure 6-7. The frmMedian form
Follow these steps to use acbDMedian in your own applications:
Import the basMedian module from 06-04.MDB into your database.
Call the acbDMedian function using syntax similar to that of the built-in DAvg function. The calling syntax is summarized in Table 6-3.
Make sure each parameter is delimited with quotes. The third parameter is optional. For example, you might enter the following statement at the debug window:
? acbDMedian("UnitPrice", "tblProducts", "SupplierID = 1")
The function would return a median value of 18 (assuming you are using the data in the 06-04.MDB sample database).
Table 6-3. The acbDMedian parameters
Parameter
Description
Example
Field
Name of field for which to calculate median
"UnitPrice"
Domain
Name of a table or query
"Products"
Criteria
Optional WHERE clause to limit the rows considered
"CategoryID = 1"
The return value from the function is the median value.
TIP
This example uses the DAO type library, and you'll need to include the reference to the most current version of DAO in your own applications in order to take advantage of this code. Use the Tools → References menu to add the necessary reference to use this code in your own database.
Discussion
The acbDMedian function in basMedian in 06-04.MDB is patterned to look and act like the built-in DAvg domain function. The algorithm used to calculate the median, however, is more complicated than what you would use to calculate the mean. The median of a field is calculated using the following algorithm:
Sort the dataset on the field.
Find the middle row of the dataset and return the value of the field. If there is an odd number of rows, this will be the value in a single row. If there is an even number of rows, there is no middle row, so the function finds the mean of the values in the two rows straddling the middle. You could modify the function to pick an existing value instead.
After declaring a few variables, the acbDMedian function creates a recordset based on the three parameters passed to the function (strField, strDomain, and varCriteria), as shown in the following source code:
Public Function acbDMedian( _
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 acbcErrAppTypeError = 3169
On Error GoTo HandleErr
Set db = CurrentDb( )
' Initialize the return value.
varMedian = Null
' Build a SQL string for the recordset.
strSQL = "SELECT " & strField
strSQL = strSQL & " FROM " & strDomain
' Use a WHERE clause only 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
' Average the two values.
varMedian = (varMedian + rstDomain.Fields(strField)) / 2