Roger's Access Blog: Domain Functions

Showing posts with label Domain Functions. Show all posts

Monday, February 7, 2011

Begin Date and End Date from Effective Date

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:

Earlier, I looked at the Difference Between query. A similar problem is the BeginDate/EndDate query. Unlike a difference between, which subtracts the value of the previous record from the value of the current record to show the difference, the BeginDate/EndDate query infers the value of the EndDate from the value of the Effective Date in the next record.

For instance, suppose I have a PriceList table where the price for each product is in effect for only a certain date range. But since maintaining a Begin Date and End Date is prone to error, I'd like to simply store an Effective Date in the record. Conceptually, I don't really need both a Begin Date and End Date. A record is in effect from its own Effective Date to the Effective Date (minus 1) of the next row. In other words, consider figure 1below. In row 1, Product 1 is $3 from 1/1/2009 (EffectiveDate) to 12/1/2009 (Effective Date of row 2 minus 1).

So from this data:

Figure 1

I'd like to a create query which would produce following result:

Figure 2

The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record following the one you're on. The only way to do it is to somehow identify the next record in terms of the data stored in the record.

For this method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, PriceID fits the bill.

The technique is similar to creating a Difference Between Query, but instead of finding the difference between a field on this row and the same field on a previous row, I want to show the value of the field on the NEXT row in the current row.

Over all, I need three steps:

1. Identify the Primary Key of next row.
2. Feed that value to a function (or query) that identifies the next date value,
3. Display the next date value on the current record (manipulating as necessary)

DMax Method

Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. They have three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

Here are the steps:

I need a DMin function to return the PriceID of the next record in the table:

DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]

Next, I'll feed that to a DLookup function, which will return the date value from the next row given number of records.

DLookUp("EffectiveDate","PriceList","PriceID = " & DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]))

Lastly, I'll subtract 1 from [EffectiveDate] of the current record and give the column an alias:

DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))) AS EndDate

The full query, looks like this:

SELECT PriceID, ProductID, EffectiveDate AS BeginDate, DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))) AS EndDate, Price
FROM PriceList
ORDER BY PriceID;

The NZ() function is needed to prevent the last row of a group from displaying an ERROR.

The Order By clause in the query is important. This will sort the query on the PriceID field. I'll need to have that order to use the criteria argument in the DMin.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

Figure 3a: Shows the calculated EndDate with NULL for the end date of the last record in the group.

This, of course, accurately represents the data because I don't know the end date of the currently effective price. Having a NULL in the field is most correct from a design standpoint. However, null values are difficult to query, so it is easier from a practical standpoint to put an actual value in the end date that is far in the future. I usually use 12/31/9999 for a future date that's not likely to become obsolete any time soon.

I can easily accomplish this with another NZ function:

SELECT PriceID, ProductID, EffectiveDate AS BeginDate, Nz(DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))),#12/31/9999#) AS EndDate, Price

FROM PriceList

ORDER BY PriceID;

Figure 3b: Shows the calculated EndDate with an artificial end date far in the future in the last record of the group.

Subquery and Outer Join Methods

This query can also be done with a correlated subquery or with an Outer Join, which I may discuss at a later date. However, you can find all three methods on my website in this sample: BeginDateEndDateQuery.mdb.

.

Thursday, January 13, 2011

Domain Function Example: Rolling Average in Query

Another value that is difficult to produce in a query, is the Rolling Average for a given number of records.

For instance, suppose I wanted to display a rolling average for the last 12 weeks for the table below:

Figure1

For Week 26, I need to display the average for weeks 15-26 (39.85). For Week 25, it would be the average for weeks 14-25 (43.85), and so forth. For weeks with less than 12 in the recordset, it will average only those weeks available. So Week 9 would only average weeks 7-9 (53.67).

In other words, this:

Figure 2

The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on -- or the previous 12, for that matter. The only way to do it is to somehow identify the previous records in terms of a Where condition. Since this Where condition must be evaluated for each line, O can do this with a domain aggregate function or a correlated subquery. In this case, two domain functions and two subqueries.

For either method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, ID fits the bill.

Domain Function Method (DCount and DAvg)

Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. DCount in particular will return the number of records in a given recordset. DAvg will return the average of a given recordset. Both functions have three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

The first step in this process is to create an unbroken sequence number for the records. It must be unbroken so I can subtract 12 from it to average the correct number of weeks. The second step produces the average.

Step1: DCount_RollingAverage1:

SELECT DCount("ID","Table1","ID <=" & [ID]) AS Sequence, tWeek, tValue
FROM Table1
ORDER BY ID DESC;

The Order By clause in the query is important. This will sort the query on the ID field. I'll need to have that order to use the criteria argument in the DCount.

Here's how it works.

For each record in the query, Access runs the DCount function. The DCount returns the number of records in the domain where the ID in the function is less than or equal to the ID in that record of the query.

So in the first record, the ID is 1. So the DCount opens the domain (essentially opens the Customers table again) and it sees that there is only 1 record whose ID is less than or equal to 1. So it returns 1.

Then it processes the second record. The ID of that record is 3, and the DCount function sees that there are only 2 records which have an ID whose value is less than or equal to 2. So it returns 2.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

Figure 3

Step2: DCount_RollingAverage2:

Now that DCount_RollingAverage1is a recordset with an unbroken sequence, I can use as it as the record source for the query that will create the rolling averages:

SELECT Sequence, tWeek, tValue, DAvg("tValue","[DCount_RollingAverage1]",
"Sequence Between " & [Sequence] & " And " & [Sequence]-12) AS [12-Week Rolling Average]
FROM DCount_RollingAverage1;

For each record in the query, Access runs the DAvg function. The DAvg returns the average for the range of values between the sequence number and the sequence number minus 12.

So in the first record, the Sequence is 26. So the DAvg opens the domain (essentially opens Table1 again) and averages weeks 15-26. Then it processes the second record, averaging weeks 14-25 and so forth.

Figure 4

Subquery Method

There is no way to combine these two queries into one. To do that, I'd need to use a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: RollingAverages.mdb.

Monday, January 10, 2011

Domain Function Example: "Difference Between" in Query

Other Examples:

Earlier, I looked at the Running Sum query. A similar (but opposite) problem is the Difference Between query. Unlike a running sum, which adds the value of a field in a record to the value of the same field in the previous record, the "difference between" query subtracts the value of the previous record from the value of the current record to show the difference.

For instance, suppose I wanted to display the difference in days between orders in the table below:

Figure1: Need to calculate the difference between records.

The difference in days between records 1 and 2 is 4, between 2 and 3 is 7, between 3 and 4 is -22, and so forth.

The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on. The only way to do it is to somehow identify the previous record in terms of a Where condition. Since this Where condition must be evaluated for each line, I can do this with a domain aggregate functions (DMax & DLookup).

For this method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, OrderDetailsID fits the bill.

The technique is similar to creating a Numbered Query or a Running Sum in a query, but instead of just counting or summing all the records above the current record, I have to find just the previous record. This adds an additional complication, which requires an additional domain function.

Specifically, I need three steps:

Use a domain function (DMax) to find the unique identifier the previous row.
Then feed that value in to another domain function (DLookup) that identifies the previous value,
And then subtract the previous value from the current value.

One way to show the difference between records is over the whole recordset. Later, I'll look at showing the difference between over groups of records.

Difference Between - Over All
Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. They have three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

Here are the steps:

I need a DMax function to return the OrderDetailID of the previous record in the table:
DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID])
Next, I'll feed that to a DLookup function, which will return the date value from the previous row given number of records.
DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID]))
Lastly, I'll subtract the this value from [OrderDate]of the current record and give the column an alias:[OrderDate]-DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID])) AS DaysBetween

The full query, looks like this:

SELECT tblOrderDetails.OrderDetailID, tblOrderDetails.OrderID, tblOrderDetails.OrderDate, nz([OrderDate]-DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & nz(DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID]),0)),0) AS DaysBetween

FROM tblOrderDetails

ORDER BY tblOrderDetails.OrderDetailID;

The two NZ() functions are needed to display a zero on the first line. Otherwise, it would return an ERROR.

The Order By clause in the query is important. This will sort the query on the OrderDetailID field. I'll need to have that order to use the criteria argument in the DMax.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

Figure 2: Shows the time difference in days between subsequent records.

Difference Between - Over Group
SELECT tblOrderDetails.OrderDetailID, tblOrderDetails.OrderID, tblOrderDetails.OrderDate,

Nz([OrderDate]-DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & Nz(DMax("OrderDetailID","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID < " & [OrderDetailID]),0)),0) AS DaysBetween

FROM tblOrderDetails

ORDER BY tblOrderDetails.OrderDetailID;

Figure 3: As the OrderID changes, the DaysBetween resets to zero.

The only difference between this query and the OverAll query is the addition of

"OrderID = " & [OrderID]

to the "Where" condition of the DMax function. This sets the value of the first record of each group to zero.

Subquery Method

As I said, these can also be done with a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: DaysBetween.mdb

Monday, January 3, 2011

Domain Function Example: Running Sum with DSum

Running Sum

One thing that is easy to do in a report, but difficult to produce in a query, is a Running Sum. A running sum adds the value of a field in a record to the value of the same field in the previous record.

In a report, there are two common types of running sums: Over All and Over Group. To create a running over all, I place a textbox control on the details section, setting the Control Source property to the field I want summed and setting the Running Sum property to Over, as in Figure 1. Figure 2 shows the results. To create one over a group (say each Order number), I'd set the Running Sum property to Over Group

Figure 1

Figure 2

But it's not so easy in a query. The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on. The only way to do it is to somehow identify the previous record in terms of a Where condition. Since this Where condition must be evaluated for each line, I can do this with a domain aggregate function (DSum) is ideal.

For this method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, OrderDetailsID fits the bill.

There are two different ways I might want to see the running sum:

Over the whole list (see Figure 3)
Over each group, where the running sum resets to zero as the Order ID changes (see Figure 5).

Running Sum Over All

There are two methods to create a running sum: DSum domain aggregate function and a correlated subquery. I'm going to concentrate on the DSum method here.

DSum

SELECT OrderDetailID, OrderID, ProductID, Price,
DSum("Price","tblOrderDetails","OrderDetailID <=" & [OrderDetailID]) AS RunningSum
FROM tblOrderDetails;

The DSum function works much the same as the DCount in the Numbered Query example, but instead of counting the records, it sums them. Unfortunately, the DSum does not return a formatted number.

Figure 3

So if you are summing a currency field, you'll have to apply the formatting yourself. To do that, we can modify the DSum function adding the Format function to display the number as currency. Like so:

Format(DSum("Price","tblOrderDetails","OrderDetailID <=" & [OrderDetailID]), "Currency") AS RunningSumFormatted

Figure 4

Running Sum Over Group

In order to get the running sum for each grouping of OrderID, all I need to do is add another condition to the "Where" argument of the DSum:

DSum("Price","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID <=" & [OrderDetailID]) AS RunningSum

In this case, "OrderID = " & [OrderID]

The complete SQL statement (including formatting):

SELECT OrderDetailID, OrderID, ProductID, Price,
Format(DSum("Price","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID <=" & [OrderDetailID]),"Currency") AS RunningSum
FROM tblOrderDetails;

Figure 5

Subquery Method

Both types of running sum can also be done with correlated subqueries. I may discuss this at a later date. However, you can find both methods on my website, in this sample: RunningSumInQuery.mdb.

Monday, December 27, 2010

Domain Function Examples: Numbered Query With DCount

Numbered Query

One interesting problem is how to create a numbered sequence in a query, that is, have each record numbered sequentially.

This is fairly easy to accomplish in an Access report. All you need to do in a report is add an unbound text box. In the control source, put =1 and set the Running Sum property to Over All.

But suppose you don't want to do it in a report. Suppose you want to do it directly in a query. There are two different ways to accomplish this. The first uses the Domain Aggregated function DCount and the second uses a Correlated Subquery. Since this series is devoted to domain functions, I'm going to concentrate on that.

Both of these methods require a unique column in the table to create the sequence on. This could be the Primary Key field or any field that has a Unique Index. In the Customers table, there are two such columns, CustID (Customer ID), which is the primary key, and CustName (Customer Name), which has a unique index.

DCount Method

Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. DCount in particular will return the number of records in a given recordset. It has three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

The specific DCount expression we're going to use looks like this:

DCount("CustID","Customers","CustID <=" & [CustID]

In the query, it will look like the following.

SELECT DCount("CustID","Customers","CustID <=" & [CustID]) AS Sequence,
CustName, CustPhone, CustID
FROM Customers
ORDER BY CustID;

The Order By clause in the query is important. This will sort the query on the CustID field. We'll need to have that order to use the criteria argument in the DCount.

Here's how it works.

For each record in the query, Access runs the DCount function. The DCount returns the number of records in the domain where the CustID in the function is less than or equal to the CustID in that record of the query.

So in the first record, the CustID is 1. So the DCount opens the domain (essentially opens the Customers table again) and it sees that there is only 1 record whose CustID is less than or equal to 1. So it returns 1.

Then it processes the second record. The CustID of that record is 3, and the DCount function sees that there are only 2 records which have an CustID whose value is less than or equal to 3. So it returns 2.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

The output of this query looks like Figure 1. Strictly speaking, you wouldn't need to show the CustID number in the query at all. However, I included it to show that while the order is the same as CustID, the sequence number does not have gaps in the numbering sequence.

You don't need to use a number field as your Order By field. You can sort on text fields and number the query as well.

If you wanted to sort on the Customer Name field (CustName), you would change the DCount to the following:

DCount("CustName","Customers","CustName <='" & [CustName] & "'")
The output would look like this:

Subquery Method
As I said, this can also be done with a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: NumberedQuery.mdb.
.

Wednesday, December 22, 2010

Domain Function Example: Simulate AutoNumber with DMax

One of the classic uses of a domain function is using the DMax() to generate your own "autonumber" field. The reason you would want to do this is if you want an unbroken sequential number field. The Autonumber data type cannot guarantee an unbroken sequence, so if you want one, you have to develop it yourself.

Single-User Application

While there are several ways to do this, one of the simplest is to use a DMax function in as the Default Value of a control on a form.

So let's say I want to generate my own sequential Product Number for a product table. I can create a form based on the Product table, and create a textbox bound to the ProductNum field. In the DefaultValue property, I would put the following domain function.

=DMax("ProductNum","Product")+1

This opens the Product table, find the largest ProductNum and add 1 to it. That's it.

Multi-user Application

It becomes a little more complex in a multi-user environment, since two or more people may try to select the same number at the same time. There's a fairly simple solution for multi-user collisions, but it requires a tiny bit of VBA code.

First of all, the field you're incrementing must be a Primary Key or have a Unique Index on it. When you try to save the record, if the number has already been used, it will throw an error (3022). You can trap this error in the OnError event of the form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Response = IncrementField(DataErr)

End Sub

This calls the IncrementField user-defined function, which looks like this:

Function IncrementField(DataErr)

If DataErr = 3022 Then

Me!ProductID = DMax("ProductID", "Product") + 1

IncrementField = acDataErrContinue

End If

End Function

So, if you have a collision, the IncrementField function will go out and grab another.

You can find a working example of both the single-user method and the multi-user method on my website here: AutonumberProblem.mdb.

Monday, December 13, 2010

Domain Functions Demystified: Criteria Expressions

In my last post: Domain Functions Demystified: Introduction, I discussed the structure and syntax of the domain functions in Microsoft Access. The basic 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)

Criteria Expressions

The third argument, the Criteria expression, is the most confusing, so it might be useful to take a closer look at them.
A Criteria Expression is an SQL Where clause without the "WHERE" keyword. Just as in a Where clause, sometimes the value of the expression needs to be delimited, sometimes it doesn't. What determines the delimiter (or lack of one) is the data type of the field.

Numeric

Numeric fields don't need a delimiter. So we can use:
"[OrderNum] = 1"
for the criteria expression. (Although remember that the entire expression must be enclosed in quotes.) Substituting a variable (in this case the name of a bound textbox on a form) for the value yields this:
"[OrderNum] = " & Me.txtOrderID)

Dates

Date fields require the date delimiter. In Access, that's the pound sign or hash mark (#). So with a hard-coded value, my criteria might look something like this:
"[OrderDate] = #1/1/2010#"
With a variable, it would look like this:
"[OrderDate] = #" & Me.txtOrderDate) & "#"

Strings

String or text values are the trickiest type and cause the most confusion. String values must have string delimiters: either quote marks (") or apostrophe ('), but since the entire argument must also be encased in string delimiters you have to somehow tell the expression evaluator which string is which. So suppose I want to use CustName in my domain function.
[CustName] = "Roger Carlson"
So if I put quotes around the whole thing, I get
"[CustName] = "Roger Carlson""
This will cause an error, however, because of the way the interpreter reads the quotes. In order to put a quote within a quoted string, you have to double the quotes:
"[CustName] = ""Roger Carlson"""
So now, I need to replace the explicit Roger Carlson with the variable:
"[CustName] = "" & Me.txtCustNum & """
But this will also cause an error because I now have two different strings that have to be concatenated and the interpreter isn't reading the quotes right again. To fix it, I have to double the inner quotes again:
"[CustName] = """ & Me.txtCustNum & """"
Now, I said you can also do this with apostrophes. So let me repeat the process:
[CustName] = 'Roger Carlson'
can be fixed surrounded with quotes this way and it will not error.
"[CustName] = 'Roger Carlson'"
But adding the variable again:
"[CustName] = ' & Me.txtCustNum & '"
gives us two incomplete strings. We need to add a quote to each:
"[CustName] = '" & Me.txtCustNum & "'"
So either:
"[CustName] = '" & Me.txtCustNum & "'"
or
"[CustName] = """ & Me.txtCustNum & """"
will work.
Now, you might ask, why would you ever use the double quote if the apostrophe will work? Well, if your data can have an apostrophe in it, (like O'Brian) you have to use the double quotes.

ASCII Code

Another possibility is to use the Chr$(34) character (which is the ASCII code for a quote) like this:
"[CustName] = " & Chr$(34) & Me.txtCustNum & Chr$(34)
This gives us the opportunity to deal with delimited values of all kinds programmatically. I suppose I could write one of my own, but Ken Getz wrote the classic routine for that. Anything I'd write would simply be a copy, so I'll show his:
(excerpted from "Microsoft Access 2.0 How-To CD" (by Getz, Feddema, Gunderloy,and Haught and published by the Waite Group Press)
Function FixUp (ByVal varValue As Variant) As Variant

'Add the appropriate delimiters, depending on the data type.
'Put quotes around text, "#" around dates, and nothing
'around numeric values.

    Dim strQuote As String
    ' strQuote contains the ANSI representation of
    ' a quote character
    strQuote = Chr$(34)
    Select Case VarType(varValue)
        Case V_INTEGER, V_SINGLE, V_DOUBLE, V_LONG, V_CURRENCY
            FixUp = CStr(varValue)
        Case V_STRING
            FixUp = strQuote & varValue & strQuote
        Case V_DATE
            FixUp = "#" & varValue & "#"
        Case Else
            FixUp = Null
    End Select
End Function
To use this function, copy it into a general module, and then call it like this:
"[CustName] = " & FixUp(Me.txtCustNum)
Using this method, it doesn't matter what data type or delimiter, the function fixes it automatically.

Mixed Apostrophe and Quotes

One last problem. What if you have both embedded apostrophes and quote marks in your string value? For instance, you have a height value which is a string with feet and inches like this: 6' 3". This will cause a problem regardless of which delimiter you use.
However, there's a solution for that too. AD Tejpal has developed a comprehensive solution, which you can find here: http://www.rogersaccesslibrary.com/forum/topic113.html
In my next post, I'll look at some in-depth examples of how Domain Functions are used.
.

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.

.

Roger's Access Blog