Link to home
Start Free TrialLog in
Avatar of Whing Dela Cruz
Whing Dela CruzFlag for Anguilla

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Haris Dulic
Haris Dulic
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Whing Dela Cruz

ASKER

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

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

Thanks! More Power to you