Application.DFirst (Access)

You can use the DFirst function to return a random record from a particular field in a table or query when you need any value from that field.

DFirst (Expr, Domain, Criteria)

    ' ***************************
    ' Typical Use
    ' Numerical values. Replace "number" with the number to use.
    variable = DFirst("[FieldName]", "TableName", "[Criteria] = number")

    ' Strings.
    ' Numerical values. Replace "string" with the string to use.
    variable = DFirst("[FieldName]", "TableName", "[Criteria]= 'string'")

    ' Dates. Replace "date" with the string to use.
    variable = DFirst("[FieldName]", "TableName", "[Criteria]= #date#")
    ' ***************************

    ' ***************************
    ' Referring to a control on a form
    ' Numerical values
    variable = DFirst("[FieldName]", "TableName", "[Criteria] = " & Forms!FormName!ControlName)

    ' Strings
    variable = DFirst("[FieldName]", "TableName", "[Criteria] = '" & Forms!FormName!ControlName & "'")

    ' Dates
    variable = DFirst("[FieldName]", "TableName", "[Criteria] = #" & Forms!FormName!ControlName & "#")
    ' ***************************

    ' ***************************
    ' Combinations
    ' Multiple types of criteria
    variable = DFirst("[FieldName]", "TableName", "[Criteria1] = " & Forms![FormName]![Control1] _
             & " AND [Criteria2] = '" & Forms![FormName]![Control2] & "'" _
            & " AND [Criteria3] =#" & Forms![FormName]![Control3] & "#")
    
    ' Use two fields from a single record.
    variable = DFirst("[LastName] & ', ' & [FirstName]", "tblPeople", "[PrimaryKey] = 7")
            
    ' Expressions
    variable = DFirst("[Field1] + [Field2]", "tableName", "[PrimaryKey] = 7")
    
    ' Control Structures
    variable = DFirst("IIf([LastName] Like 'Smith', 'True', 'False')", "tableName", "[PrimaryKey] = 7")
    ' ***************************

Arguments

The following arguments are required:

Expr (String) - An expression that identifies the field from which you want to find the first or last value. It can be either a string expression 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.

Domain (String) - A string expression identifying the set of records that constitutes the domain.

Optional arguments

The following argument is optional

Criteria (String) - An optional string expression used to restrict the range of data on which the DFirst function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the wrd WHERE. If criteria is omitted, the DFirst function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DFirst function returns a Null.