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