Domain Aggregate Functions
Domain Aggregate functions are specific to Microsoft Access because they are used to retrieve data from tables. Because you can't assign the results of a query directly to a variable, you must use Domain Aggregate functions to retrieve that data. There are other ways besides Domain Aggregate functions that will be covered later in this book. The advantages of the Domain Aggregate functions are that they can accept a set of criteria to retrieve just the data needed. All the Domain Aggregate functions use a similar syntax, where expression is the name of the field in a table or query, domain is the name of the table or query, and criteria is a comparison to define which record to extract the value from:
Function("[expression]", "domain", criteria)
Notice that the expression is usually surrounded by quotes and brackets and that the domain is also surrounded by quotes. I'll list some of the more commonly used Domain Aggregate functions.
The DLookup Function
The DLookup function is used to retrieve a value from a single field in a table or query. The following example returns the last name of the contact for Supplier G from tblSuppliers in the Inventory application:
DLookup("[LastName]", "tblSuppliers","[Company] = '" & "Supplier G" & "'")
The DLookup function retrieves that value from the first record matching the criteria. Because Company is a Text data type, you must concatenate the single quotes around the company name. If you are comparing a Numeric data type, no quotes are needed, and a Date/Time data type requires octothorpes (#) to delimit the value to be searched for.
The DCount Function
The DCount function is used to count the number of records in a table or query that match your criteria. An example of the DCount function follows:
DCount("*","tblEmployees","[Jobtitle] = 3")
This returns a result of 6 because there are six employees whose job title is Sales Representative.
The DMax/DMin Functions
The DMax and DMin functions return the highest or lowest values in the domain according to the criteria listed. An example of the DMin function follows:
This returns 3/22/2006 4:02:28 PM, which is the earliest transaction in the Transactions table.