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.

Solution 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

Solution 2

Here is a way to print out the columns of a table.

Dim cat

Set cat = CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = db 'db is the adodb.connection object

Dim tbl
Dim clm
For Each tbl In cat.Tables
   For Each clm In tbl.Columns
      Debug.Print (clm) ' Prints the column name from the table
   Next
Next

Comments

[PlayHardGoPro]

I want to check only if that field is empty or not, not the whole recordset ;x

[PlayHardGoPro]

I found out the error. I can't compare it to a nullValue because it's not null, actually it does not even exists on the recordset, the field it was supposed to search("fieldname") is not on the recordset... -.-" Is there a way to preview it ?

[Calvin]

Preview what? The recordset?

[PlayHardGoPro]

Look, the field i'm searching, e.g: If (rs.fields("Name") <> "") Then ... But the field Name does NOT exists on that recordset. That's why the error is firing... so I need a why to check that field on the recordset without firing an error.

[Calvin]

I edited my answer with a not so elegant solution to get column names. You could use this initially to store a list of them and then check if the name is in the list. There could be a more elegant solution, but this is all I've found for now.

[peterh]

Although the code is appreciated, it should always have an accompanying explanation. This doesn't have to be long, but it is expected.