Access/VBA Library

QueryValue

Executes a SELECT-query and returns the value of the first column of the first record.

Syntax

QueryValue( query [, defaultValue] )
Parameters
Name Type Description
query String Required. A valid SELECT-statement.
defaultValue Variant Optional. Value to be returned when the query fails or has no output.
Default: Null
Return value

Variant : The value of the first column of the first record or defaultValue when an error occured or the query has no output.

Code
Function QueryValue(query As String, _
                    Optional ByVal defaultValue = Null)

On Error GoTo QueryValue_Error
  
    Dim rs As Recordset
    Dim db As Database: Set db = CurrentDb
    
    Set rs = db.OpenRecordset(query, dbOpenSnapshot, dbReadOnly)

    If rs.EOF Then
        QueryValue = defaultValue
    Else
        rs.MoveFirst
        QueryValue = rs.Fields(0).Value
    End If
    
QueryValue_Exit:
    On Error Resume Next
    rs.Close
    Exit Function
    
QueryValue_Error:
    QueryValue = defaultValue
    Resume QueryValue_Exit

End Function

SQLtag:SQLDatabasetag:Database

Remarks

  • Use this function when you are sure the query will return juist one value.

Changelog

Date Changes
2018-04-02 Performance change: open recordset as read-only snapshot.