• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Gets the value of any field with given criteria

Gets the value of any field with given criteria

Gets the value of any field with given criteria

API Declarations


'NOTE: db is assumed for Database
'And DAO is used as Reference



Rate Gets the value of any field with given criteria

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

                    sTable As String, sCheckField As String, _
                    vCheckValue As Variant, sTargetField As String, _
                    Optional sComparisionChars As String = "=", _
                    Optional bNotOperator As Boolean = False, _
                    Optional sExtraSQL As String = "", _
                    Optional vDefaultReturn As Variant = -1, _
                    Optional lOUT_ERROR As Long = 0 _
                ) As Variant

    Dim r As Recordset, s As String

On Error GoTo errGFV

    If Not (sComparisionChars = "=" Or _
            sComparisionChars = ">" Or _
            sComparisionChars = "<" Or _
            sComparisionChars = ">=" Or _
            sComparisionChars = "=>" Or _
            sComparisionChars = "<=" Or _
            sComparisionChars = "=<") Then
        lOUT_ERROR = -1
        Exit Function
    End If

    GetFieldValue = vDefaultReturn

    s = "SELECT " & sTargetField & " " & _
        "FROM " & sTable & " " & _
        "WHERE " & IIf((sExtraSQL = ""), "", sExtraSQL & " AND ") & _
        IIf(bNotOperator, "NOT ", "") & sCheckField & sComparisionChars

    If VarType(vCheckValue) = vbString Then
        s = s & "'" & vCheckValue & "'"
    ElseIf VarType(vCheckValue) = vbDate Then
        s = s & "#" & vCheckValue & "#"
    Else
        s = s & vCheckValue
    End If

    Set r = dB.OpenRecordset(s)

    If r.EOF Then
        Set r = Nothing
        Exit Function
    End If

    GetFieldValue = r.Fields(0).Value
    Set r = Nothing
    Exit Function
errGFV:
    lOUT_ERROR = Err.Number
    Err.Clear
    GetFieldValue = vDefaultReturn
    Set r = Nothing
End Function