Access Cookbook

Find the Median Value for a Field

Problem

You need to calculate the median for a numeric field. Access provides the DAvg function to calculate the mean value for a numeric field, but you can’t find the equivalent function for calculating medians.

Solution

Access doesn’t provide a built-in DMedian function, but you can make one using VBA code. This solution demonstrates a median function that you can use in your own applications.

Load the frmMedian form from 06-04.MDB. Choose the name of a table and a field in that table using the combo boxes on the form. After you choose a field, the median value will be calculated and displayed in a text box using the acbDMedian function found in basMedian (see Figure 6-7). An error message will be displayed if you have chosen a field with a nonnumeric data type; the string “(Null)” will be displayed if the median value happens to be Null.

Figure 6-7. The frmMedian form

Follow these steps to use acbDMedian in your own applications:

1. Import the basMedian module from 06-04.MDB into your database.

2. Call the acbDMedian function using syntax similar to that of the built-in DAvg function. The calling syntax is summarized in Table 6-3.

   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 ...