Hack 74. Substitute Domain Aggregate Functions for SQL Aggregate Functions

Reduce the amount of code you enter and still get the same results.

Within VBA code, it is a common practice to tap into the ADO objects and use some SQL to query data in the database. Because SQL is the de facto standard for querying data, following this route is understandable. However, sometimes you don't need to query data in this way.

For example, if you need to process individual records, using ADO and SQL makes sense. A recordset is created that is typically scrolled through using the MoveNext method within a Do Until loop or similar construct.

On the other hand, ADO and SQL are sometimes used just to get an aggregate value from a set of records. In this situation, the individual records are of no concern. Instead, you're looking for a summary, such as a sum, a count, or an average.

8.4.1. The Code

Example 8-1 shows a routine that uses ADO and SQL to return the sum of some invoice amounts.

Example 8-1. Using ADO and SQL to return a sum

Sub get_SQL_Sum( )
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim rs As New ADODB.Recordset
  rs.Open "Select Sum(Amount) As SumOfAmount From Invoices" & _
    " Where InvoiceDate=#12/10/04#", _
           conn, adOpenKeyset, adLockOptimistic
  MsgBox rs.Fields("SumOfAmount")
  rs.Close
  Set rs = Nothing
  Set conn = Nothing
End Sub

The SQL statement includes the SQL aggregate Sum function. Also, the sum of the amounts is from a set of records filtered to a single invoice date of 12/10/04. The code in Example 8-1 requires creating ADO objects and then destroying them afterward (by setting them to Nothing).

You can boil down all this to a single line using a domain aggregate function.

8.4.2. Boiling Down the Code

Domain aggregate functions provide the same results as SQL aggregate functions. However, whereas you need to somehow embed SQL aggregate functions into a SQL statement, you can code domain aggregates independently.

Example 8-2 shows how a short routine using the DSum domain aggregate function replaces the code in Example 8-1.

Example 8-2. Using DSum to return the sum

Sub get_Domain_Sum( )
  Dim amount As Single
  amount = DSum("[Amount]", "Invoices", "[InvoiceDate] = #12/10/04#")
  MsgBox amount
End Sub

Other than dimensioning the amount variable and using a message box to display the result, the code requires just one statement:

	amount = DSum("[Amount]", "Invoices", "[InvoiceDate] = #12/10/04#")

The arguments handed to DSum are the field to sum, the domain (a table or Select query), and any filtering. The third argument works in the same manner as the SQL Where clause.

You can even enter complex criteria for the third argument. For example, this line of code returns the sum of amount when the invoice date is 12/10/04, the customer is Anderson, and the location is either Chicago or Dallas:

   amount = DSum("[Amount]", "Invoices", "[InvoiceDate] = #12/10/04# And 
   Customer]='Anderson' And ([Location]='Chicago' or [Location]='Dallas')")

8.4.3. Domain Aggregate Functions

There are several domain aggregate functions:

DAvg: Returns the average of the values in the field in the first argument.
DCount: Returns the count of records.
DLookup: Returns the value of the first field in the first record that matches based on the criteria in the third argument.
DFirst and DLast: Returns the value of the field in the first argument from the first or last record.
DMin and DMax: Returns the minimum or maximum value of the field in the first argument from among the records.
DStDev and DStDevP: Returns the standard deviation of the values in the field in the first argument.You use DStDev with a sample from a population. You use DStDevP with the full population.
DSum: Returns the sum of the values in the field in the first argument.
DVar and DVarP: Returns the variance among the values in the field in the first argument.You use DVar with a sample from a population. You use DVarP with the full population.

All the domain aggregate functions work with the same three arguments: the field being evaluated, the domain, and the criteria. Look up these functions in the Access Help system if you want more information. Integrating them into your procedures is a great way to retrieve quick summaries of data with just single lines of code.