Check a recordset for an empty field

vb6 recordset
PlayHardGoPro picture PlayHardGoPro · Oct 29, 2013 · Viewed 31.7k times · Source

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

jac picture jac · Oct 29, 2013

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

Related questions

VB6 ADO Recordset Limit

Please don't laugh because I'm asking a VB6 question? I have to do some maintenance work. Does anyone know what the maximum number of rows can be returned to a VB6 ADO Recordset? If there is a limit, is there …

Read More
Does VBA have Dictionary Structure?

Does VBA have dictionary structure? Like key<>value array?

Read More
VBA array sort function?

I'm looking for a decent sort implementation for arrays in VBA. A Quicksort would be preferred. Or any other sort algorithm other than bubble or merge would suffice. Please note that this is to work with MS Project 2003, so should …

Read More