MS Access supports several functions for manipulation of data. One such set of functions that are available throughout Access maybe it in Queries or VBA are the Domain functions.
SELECT Count(FirstName) As CountOfFirstName
FROM Customers
WHERE FirstName = 'Paul';
So if you are someone who is very lazy (like me !) or you have very limited knowledge of writing Queries or even if you think writing Queries is a bore, then you are in luck. Someone thought to just do this is a simpler way, just tell the compiler what you need, where to look and it gives you a result. That is the discovery of the Domain functions.
DFunctionName("expression", "domain", "criteria")
For the example we are dealing with, the function we would use be Count.
DCount("*", "Customers", "FirstName = 'Paul'")
If you have seen the above example, I have used the * as the Column/Field name, this is because, specifying a particular column in a Count function would slow down the compiler. To be efficient using a * would be faster.
What are Domain Functions?
Domain Functions are some inbuilt functions that can access the Data in Tables. In simple terms Domain functions are simplified Queries. If you are ever in a position to need to Count the number of Customer in the table who has their first name as 'Paul', you start typing..SELECT Count(FirstName) As CountOfFirstName
FROM Customers
WHERE FirstName = 'Paul';
So if you are someone who is very lazy (like me !) or you have very limited knowledge of writing Queries or even if you think writing Queries is a bore, then you are in luck. Someone thought to just do this is a simpler way, just tell the compiler what you need, where to look and it gives you a result. That is the discovery of the Domain functions.
Identifying & understanding a Domain function
In general Domain functions start with the letter 'D'. There are about 8 Domain functions.- SELECT Domain functions
- DFirst
- DLast
- DLookup
- AGGREGATE Domain functions
- DAvg
- DCount
- DMax
- DMin
- DSum
Things to know !
- All parameters are treated as Strings. So make sure you enclose them in Double Quotes.
- A Domain function can return only one Value even if the records returned are more than one.
- A Domain function can return a Null value, if the condition is not met. So be prepared to capture that scenario.
- Domain functions can be heavy, so best to limit its usage.
- The criteria should be evaluated to a String, based on the Data type of the field the condition would vary. Look at the common usage syntax for more information.
Anatomy of a Domain function
All domain function can be broken down into three parts
- expression - the field name where the function needs to look at
- domain - name of the table/query where the function needs to be applied
- criteria - (Optional) if you are looking for something in specific, then you add it here.
DFunctionName("expression", "domain", "criteria")
For the example we are dealing with, the function we would use be Count.
DCount("*", "Customers", "FirstName = 'Paul'")
If you have seen the above example, I have used the * as the Column/Field name, this is because, specifying a particular column in a Count function would slow down the compiler. To be efficient using a * would be faster.
Common Usage Syntax
The first example of every Data Type would be written basically for normal use, the second uses Form reference and how to concatenate them into the Criteria.
For Inbuilt constants/Numeric,
DLookup("FirstName", "Customers", "ActiveCustomer = True")
DFirst("LastName", "Customers", "CustID = " & Me.IDTxtBox)
For Strings,
DCount("*", "Customers", "FirstName = 'Paul'")
DAvg("annualPay", "Customers", "City = '" & Me.cityTxtBox & "'")
For Dates,
DSum("QuantityOrdered", "Orders", "dateOrdered = #12/01/2013#")
DMax("QuantityOrdered", "Orders", "dateOrdered = " & _
Format(Me.dateTxtBox, "\#mm\/dd\/yyyy\#"))
