Invalid use of null

[abu taher]

Set cn = New ADODB.Connection
    
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\home.mdb"
  cn.Open
    Set rs = New ADODB.Recordset
    
    
rs.Open "select sum(income),sum(Spendecher) from main where date between #" & DTFr.Value & "# and #" & DTTo.Value & "#", cn

Label3.Caption = rs(0)  'Your total Income..
Label5.Caption = rs(1)  'Your Total Expenses..
rs.Close
a = Label3.Caption
b = Label5.Caption

the above code works fine. but after few days it show a msg, " Invalid use of null"
Why this msg show? and highlight this

Label3.Caption = rs(0)

then I change the code. I write

  If Not IsNull(rs.Fields(0)) Then
   
        Label3.Caption = rs.Fields(0)
   
      End If

  If Not IsNull(rs.Fields(1)) Then
   
        Label5.Caption = rs.Fields(1)
   
      End If

now show "Type mismatch" and highlight

a = Label3.Caption

any body help me why the 1st msg show and now what I need to do?

[dee-u]

You can append a null string to the value to solve the 'invalid use of null' problem. And you should check if the Caption is numeric before setting it to your variable.

Label3.Caption = rs.Fields(0) & vbNullString
Label5.Caption = rs.Fields(1) & vbNullString

If IsNumeric(Label3.Caption) Then
 a = Label3.Caption
End If

[Siddharth Rout]

a = Label3.Caption

How is "a" declared?

[abu taher]

You can append a null string to the value to solve the 'invalid use of null' problem. And you should check if the Caption is numeric before setting it to your variable.

I declare variable

dim a as integer
dim b as integer

now that msg not show. but label3.caption not show any thing. another thing why this msg not show at 1st time and why show now? I didn't make any change, then why?

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

now that msg not show. but label3.caption not show any thing.

If you are using what dee-u showed (and you should), that is entirely expected - because there is no number to put in it.

another thing why this msg not show at 1st time and why show now? I didn't make any change, then why?

Your original code assumed that you would always get a record containing numbers back from the SQL statement, which is not the case - you will always get a record (because you are using Sum), but the values will be Null if there are no records in the table that match your Where clause:

where date between #" & DTFr.Value & "# and #" & DTTo.Value & "#"

As you have called your field Date (which is the name of a function which returns the current date) it is hard to predict if the field will be used rather than the function - so you should change the field name if you have any choice.

On top of that, you haven't formatted the values of DTFr.Value and DTTo.Value properly... For how it should be, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)

[GaryMazzone]

I would have assumed that if there were no records in the DB for the specified range you would get 0 back for the SUM() function.

[abu taher]

you should check if the Caption is numeric before setting it to your variable.

plz explain this line.

GaryMazzone: but it have record.

[si_the_geek]

I would have assumed that if there were no records in the DB for the specified range you would get 0 back for the SUM() function.

You've got me thinking, and I'm not entirely sure... I know there have been some situations with a Sum that I got Null back, but I can't remember any of the details.

[techgnome]

Correct. You'll get null back on aggregates when no rows were returned. 0 is an actual value. NULL means Unknown. Since no rows are returned, the only possible outcome is unknown.

-tg