DFirst, DLast Functions (SQL Only)
See Also Example
Return a field value from the first and last record in a specified set of records (domain).
DFirst( expr, domain [, criteria] )
DLast( expr, domain [, criteria] )
The DFirst and DLast functions use the following arguments.
expr String expression identifying the field that contains the data you want to return, or an expression that performs calculations using the data in that field. Operands in expr can include the name of a table field, or a Visual Basic function (which can be intrinsic but not a user-defined function or one of the other domain aggregate or SQL aggregate functions).
domain String expression identifying the records that constitute the domain. It can be a table name, a query name, or an SQL expression that returns data.
criteria Optional string expression used to restrict the range of data on which DFirst or DLast is performed. For example, criteria could be the WHERE clause in an SQL expression (without the word WHERE). If criteria is omitted, expr is evaluated against the entire domain.
DFirst and DLast are similar to DLookup. These functions return a field value from the record that satisfies criteria. DFirst and DLast return values from the first and last occurrence according to the order of records in domain. If domain is an indexed table, the order follows the current index. Otherwise, the order follows the actual order of the records.
If more than one record satisfies criteria, DFirst returns the field value from the first occurrence.
You can use DFirst and DLast to determine the limits of a domain. For example, the fields returned by DFirst and DLast might contain order dates that you could use in a report title to indicate the range for orders.
If the criteria argument contains non-numeric text other than field names, you must enclose the text in single quotation marks. In the following example from an Orders, Ship Country is the name of a field, and UK is a string literal.
X = DFirst("[Freight]", "Orders", "[Ship Country] = 'UK'")
Y = DLast("[Freight]", "Orders", "[Ship Country] = 'UK'")
Z = DLast("[Freight]", "Orders", "[Ship Via] = 1")
If no record satisfies criteria, or if domain contains no records, DFirst and DLast return a Null.