You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field. Use the DFirst and DLast functions in a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.), module, query expression, or calculated control (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on a form or report.
DFirst(expr, domain [, criteria] )
DLast(expr, domain [, criteria] )
The DFirst and DLast functions have these arguments:
||Required. An expression that identifies the field from which you want to find the first or last value. It can be either a string expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) identifying a field in a table or query, or an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
||Required. A string expression identifying the set of records that constitutes the domain.
||Optional. A string expression used to restrict the range of data on which the DFirst or DLast function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DFirst and DLast functions evaluate expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DFirst and DLast functions return a Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.).
Note If you want to return the first or last record in a set of records (a domain (domain: A set of records that is defined by a table, a query, or an SQL expression. Domain aggregate functions return statistical information about a specific domain or set of records.)), you should create a query sorted as either ascending or descending and set the TopValues property to 1. For more information, see the TopValues property topic. From a Visual Basic for Applications (VBA) module, you can also create an ADO Recordset object and use the MoveFirst or MoveLast method to return the first or last record in a set of records.