Null Value Not Recognized

[TheSearcher]

The code below returns a null string (as it should) in query design.
However, in my VBA code the vartype for rs("Number_UnitsBilled") is 9 which indicates that it is returning an object. Therefore, the program crashes with a "No current Record" error in the "Else" statement. How can this be?

sql2 = "SELECT tbl_Billing.Number_UnitsBilled, tbl_Billing.ID "
sql2 = sql2 & "From tbl_Billing "
sql2 = sql2 & "WHERE (tbl_Billing.Client_Id = '" & Client & "') "
sql2 = sql2 & "AND (tbl_Billing.Date_Of_Service = #" & DOS & "#) "
sql2 = sql2 & "AND (tbl_Billing.Payer = 'MLTSS') "
sql2 = sql2 & "AND (tbl_Billing.Therapy = 'Structured Day');"
Set rs2 = db.OpenRecordset(sql2)
    
MsgBox "Vartype: " & VarType(rs2("Number_UnitsBilled"))
'Vartype 9 = Object
    
If IsNull(rs2("Number_UnitsBilled")) = True Then
    SDBilled = 0
Else
    SDBilled = rs2("Number_UnitsBilled")
End If

 

[Josef P.]

IsNull(FieldReference) => false

If IsNull(rs2("Number_UnitsBilled")) = True Then
vs
If IsNull(rs2("Number_UnitsBilled").Value) = True Then

=>

if rs2.eof then
     SDBilled = 0
else
     SDBilled = Nz(rs2("Number_UnitsBilled").Value, 0)
end if

 

[TheSearcher]

Nevermind. I just replaced "If IsNull(rs2("Number_UnitsBilled")) = True Then" with "If rs2.recordcount = 0 then"
Now all is good. Dumb mistake.
@Josef P - Thanks for responding.

 

[MarkK]

In boolean math, comparing a value to True has no effect. It's like multiplying by one. This is more succinct code...

If IsNull(rs2("Number_UnitsBilled").Value) Then

Compare with Josef P.'s test for EOF...

if rs2.eof then

Recordset.EOF is a boolean property, so no need to compare with True.

Also, to determine the type of a variable, consider using Typename() rather than VarType(). Typename() return the name of the type, so ...

MsgBox "Type is: " & Typename(rs2("Number_UnitsBilled"))

...would have returned "Field2", and you would have noticed right away that you were testing not the Recordset.Field.Value, but the Recordset.Field object itself.

hth : )