Check a recordset for an empty field

Question

I'm trying to pre-view if a field of the recordset is empty/null or not.

If IsNull(rs.Fields("fieldname")) = True Then ...

If IsNull(rs.Fields("fieldname")).Value = True Then ...  

if IsNull(rs.Fields("fieldName").Value) Then...

All of these methods fires up an error... Why? How may I check if the recordset is null before I assign it's value to a variable.

Answer 1

If I understand correctly, you want to ensure that a field exists in the recordset. If that is correct, you need to either iterate the fields looking for the field you are searching for, or try to directly access the field and trap any errors. Here is a method that iterates the field collection and returns True if the field exists.

Public Function FieldExists(ByVal rsRecSet As ADODB.Recordset, ByVal FieldName As String) As Boolean
    Dim fld As ADODB.Field
    Dim Rtn As Boolean

    If Not rsRecSet Is Nothing Then
        For Each fld In rsRecSet.Fields
            If StrComp(fld.Name, FieldName, vbTextCompare) = 0 Then
                Rtn = True
                Exit For
            End If
        Next fld
    End If

    FieldExists = Rtn

End Function