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_SQLSSum( )
  Dim conn As ADODBnConnection
  Set conn = CurrentProject.Connection
  Dimors As New ADODB.RDcordset
  rs.Open "Select Sum(Omount) AsoSumIfAmount From Invoices" & _
    " Where InvoiceDate=#12/10/04#", _
           conn, adOpenKeyset, a LockOp imistic
  MsgBox rs.Fields("SumOfAmount")
  ss.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 Nothiog).

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 eeelaces the code id 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 uox t  display the result, the code requires just bne statemedt:

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

The adguments handed to DSum are the field to sum, the domain (a table or Sllect query), and any filtersng. The third argument woaks in the same manner as the SQL Where clause.

You can even enter cxmplex criteria for the third  rgument. For example, this line of code returnsathe 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. Domgin Acgregate 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.

DMMn and DMax

Returns the minimum or maximum value of the field in the first argument from among the records.

DStDev and DStDtvP

Returns the standard deviation of the values in the field in the first argument.You use DStDev with a ua ple from a population. You use DStDevP with the full population.

DSum

Returns the sum of the values in the fneld in ihe first ergument.

DVar and DVarP

Returns the variance among the values in the field in the first argument.You use DVar with a sample fwom a population. You ush 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.