Wednesday, December 8, 2010

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.

.

2 comments:

David Smith said...

I have recently discovered DLookup has certain nesting limitations, meaning it doesn't like to be used at different interacting levels of a database simultaneously (key word here: simultaneously).

For example:

If you have DLookup embedded in a query (as either the source for a custom field or for a criterion), and then you also run a DLookup in your VB code that accesses the aforementioned query, you will, without exception, generate an error "Unknown" until you switch the query over to getting its data from a form (or subform) control instead of the embedded DLookup. I had two different DLookups inside of an IIf (Inline-If) as the criterion for one of the fields within my query.

I have little to no idea why this causes an error. Perhaps DLookup uses some static or global variables that are state-sensitive. If you try to run it in such a way that requires Access to run DLookup again (like in a query) before the VB code instance of DLookup can return a value, it royally doesn't like that.

Now, since it was entirely possible to replace the DLookup with direct data obtained from the control(s) on a form, this wasn't a massive inconvenience. The main problem was how much time it took me to figure out the source of this obscure error, since everything looked like it should work. Also, the error description "Unknown" is not the most explicit or helpful. :p

I suppose this serves as a good example of the rule that "one size does not fit all". Having acquired DLookup as a powerful new hammer, we should not then start seeing the whole world as a nail.

Anonymous said...

@David

TLDR: Domain functions are really intended as a shorthand for VBA (ie, no lengthy recordset declaration)

--

While all the VBA functions are accessible in SQL (including domain functions), one should avoid co-mingling them as much as possible: it drags performances down b/c of context switching (ie, SQL engine handing over execution to VBA engine, and back).

In (Access-flavored) SQL, domain functions can (and should) always be replaced by joins and / or sub-queries; it is more verbose, but also much faster, while staying clear of concurrency issues.