Have you ever wanted to just drop a value from a table onto a form, or get at a table value in some program code without having to using recordsets?. Domain functions are a useful tool for extracting single values from tables or queries based upon some selection criteria.

Record Sources

The problem of displaying a value on a form deserves a little explanation. Both forms and reports can have "Record Source" properties, which describe the location from which data will be pulled onto the form.

The record source can be either a Table, Query or SQL String. The problem comes when you want to display a piece of data on the form that is not easily related to the data provided by the forms record source.

Domain functions provide a very simple mechanism to display the data on the form. All you need to do is add a text box, and use a calculation involving a domain function in the text box.

In the example below we have a table called tblSystem containing 1 row of data, which has a column called SystemVersion, and we want to display the system version on a form.

Top of Page

Domain Functions

There are several different domain functions to choose from : DAvg, DCount, DLookup, DFirst, DLast, DMin, DMax, DStDev, DStDevP, DSum, DVar, DVarP.

The Domain function can have three parts to it for example DCount(expr, domain[, criteria]), the expr is the expression to return, the domain is the table or query, and the criteria is the restriction on the data, which allows a particular row in the data to be located.

Top of Page

Easy Programming

Domain functions enable program code to quickly retrieve a value, as shown below.

With a domain function like DLookup you need to take care that you either use a variant variable (because the function could return null), or use the Nz function to return an empty string when the value is null.

Top of Page

Over Use Of Domain Functions

If you use a domain function on a continuous form then you may find that there is an unacceptable performance penalty in doing this. So you need to decide for yourself whether this approach is right for your application.

The above example also shows how the value from the [City] control is used in the criteria for the domain function, counting only those customers in each city. Notice how the value must be surrounded by single quotes as shown below:-

=DCount("*","Customers","[City] = '" & [City] & "'")

This is because the criteria is for a string, it were numeric then we would would have no quotes, and if it were a date we would need to use # symbols (the date must always be in US mm/dd/yyyy format!).

=DCount("*", "Orders", "[OrderDate] = #07/04/1996#")

In the above example you could have used the following query as a record source for the form.

BUT, the form will now be read only, and the example using the domain function does not suffer from this problem.

Top of Page

DFirst & DLast Gotcha's

Care needs to be taken when relying on DFirst and DLast functions. The question is, what is it first or last with respect to?

In the following example the query below correctly gives the last account and the maximum date.

But if we run this after having compacted the database we get a different answer.

This has happened because the DateOf field is the Primary Key, and compacting a database re-orders the records by the primary key. If we had always added incrementing dates we would have been OK, but as this example shows it is possible to get some strange side-effects in unusual circumstances.

The code can be downloaded by clicking here.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz