Domain Functions Demystified

Introduction

In Microsoft Access, domain functions work like mini-SQL statements that can be used in queries, forms, reports, or code. Domain functions can also be used in places where SQL statements cannot, like the control source of a textbox. Like an aggregate (or totals) query, the domain functions build a resultset (that is, a set of records) and then apply a function to it. Unlike a query, however, a domain function can return ONLY one value.

Access has several built-in domain functions, the most common of which are: DCount(), DLookup(), DSum(), DAvg(), DMax(), DMin(), DFirst(), and DLast().

• DCount() returns the number of records of the resultset.
• DLookup() returns the value of a field in the resultset. If the resultset has multiple values, it returns the first one.
• DAvg() averages the values of the indicated field of the resultset.
• DMax() and DMin() find the highest and lowest values (respectively) of the resultset.
• DFirst() and DLast() finds the first and last values (respectively) of the resultset.

Note: At first glance, DFirst and DLast appear to be same as the DMax and DMin, but it they're really very different. DFirst returns the very first record in the recordset, which may not be the minimum value. Likewise, the last record in the recordset may not be the maximum. In general, I avoid DFirst and DLast as being less than useful.

One popular myth is that domain functions are slower than other methods. This is not true.  In some cases, the domain functions can be as fast or faster than other methods.  If performance is an issue, you should test both methods to and see which performs better.

Syntax

Domain functions have three arguments (the third is optional, however). The syntax is as follows:

DFunctionName("<Fieldname >", "<RecordSource>", "<Criteria Expression>")

• Fieldname refers to the field against which the function will be applied.
• RecordSource refers to a table or query from which the records will be pulled.
• Criteria Expression is basically an SQL Where clause without the WHERE keyword. (optional)

Each argument must be a string expression and must, therefore, be surrounded with quotes. This can be a problem when the Criteria Expression must also have quotes in it. I'll get to that a bit later.

Just like SQL Where clauses, some values must be delimited and others are not. For instance:

• Numeric values do not need delimiters (DLookup example below).
• Date values need the # delimiter around them (DMax example below).
• Strings need either the apostrophe (') or quote (") delimiters (DSum example below).

Examples of Simple Domain functions

• DCount("EmpID", "tblEmployee")Counts the number of records in the Employee table
• DLookup("SSN", "tblEmployee", "[EmpID] = 16")Returns the social security number of employee number 16.
• DMax("OrderDate", "tblOrders", "[OrderDate] < #1/1/2009#")Returns the latest order date from the Orders table which is before 1/1/2009
• DAvg("Cost", "tblProduct", "[Category] = 'printer'")
  Returns the average cost of all printers from the Products table.
• DSum("Cost", tblProduct", "[Category] = 'printer' AND [Manufacturer] = 'Epson'"or
  DSum("Cost", tblProduct", "[Category] = ""printer"" AND [Manufacturer] = ""Epson"""
  Returns the total cost of Epson printers from the Products table.

Because the entire Criteria Expression must be string delimited, the expression evaluator will get confused when using the quote to delimit a sting value, so you have to use two quote marks ("") in place of one ("). I'll discuss this in a later post.

Variables in Criteria Expressions

So far, I've just used hard-coded values in the Criteria Expression. That's not the most useful application of domain functions. Domain functions become most useful when you use a variable for the value in the Criteria Expression.

For instance,

DSum("Price", "tblOrderDetails", "[OrderNum] = 1")

will return the total price for a particular order (1). (The value is not delimited with quotes in this case because order number is a numeric field.) But if I put this expression in the control source of a textbox on a continuous form, it would always show the same value, regardless of the other values on the screen.

However, if I use a variable in place of the hard-coded order number, it becomes much more useful. Taking the OrderID from each record, gives me:

DSum("Price", "tblOrderDetails", "[OrderNum] = " & Me.txtOrderID)

In the first example, it is calculating the total for order 1 regardless of the other values in the row. In the second, it calculates the total based on the order number of that row.

Criteria expressions in domain functions are confusing, so it might be useful to take a closer look at them, which I will do in my next post: Domain Functions Demystified: Criteria Expressions.

.