Avoid Null in Getting Sum

How can i escape from error Called " Invalid use Of Null"?
if i set the range out from the data an error appear " Invalid use of Null"
if i press Compute an error appear once the date is set out from the data..
What shall i do to avoid this?
thanks!!

Private Sub Income()
Call OPEN_Win("FamilyTEXP", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where D_Date >= '" & CStr(eFrom1.Value) & "' and D_Date <= '" & CStr(eTo2.Value) & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
 
       Gincome.Caption = (rs("TotalAmount"))
       Gincome.Caption = Format(Gincome, "###,###,##0.00")
    End With
    Set rs = Nothing
End Sub
 
Private Sub Compute_Click()
Income
End Sub

Open in new window

Whing Dela CruzAsked:
Who is Participating?
 
Haris DjulicConnect With a Mentor Commented:
give 0 if it is null


ql = "SELECT ISNULL(SUM(TheIncomAmount),0.00) as TotalAmount FROM MyIncome where D_Date >= '" & CStr(eFrom1.Value) & "' and D_Date <= '" & CStr(eTo2.Value) & "'"

Open in new window

0
 
Whing Dela CruzAuthor Commented:
Hi!
I tried it but still not working, if i  backward the date Run-time error '94'" Invalid use of Null

heres the feild;
    [TheXcode] [char] (12) NULL ,
    [TheIncomDes] [varchar] (50) NULL ,
    [D_Date] [datetime] NULL ,
    [TheIncomSource] [varchar] (50) NULL ,
    [TheIncomAmount] [float] NULL



Private Sub Income()
Call OPEN_Win("FamilyTEXP", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT ISNULL(SUM(TheIncomAmount),0.00) as TotalAmount FROM MyIncome where D_Date >= '" & CStr(eFrom1.Value) & "' and D_Date <= '" & CStr(eTo2.Value) & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
 
       Gincome.Caption = (rs("TotalAmount"))
       Gincome.Caption = Format(Gincome, "###,###,##0.00")
    End With
    Set rs = Nothing
End Sub

Open in new window

0
 
Whing Dela CruzAuthor Commented:
Hi!
I'm Sorry but the code is working my problem is only if i Write the
eto2.value into 2/14/200 an error appear invalid use of NULL .
Can i prevent this?
Thanks

0
 
Whing Dela CruzAuthor Commented:
Thanks! More Power to you
0
All Courses

From novice to tech pro — start learning today.