Sum Of A Field In Vb6.0

[mesubham]

I have created a project where I created a form (scm.frm). In this form I put a caption (label9). This caption will show the total amount in a database field. It is working properly, example if I want to know the total transaction in November month, it is showing properly. If this month has transaction NIL, it also shows. But when I want to see the transacton NIL month before I saw a total amount transacted month, it doesn't change the value in the form.

More specifically, I have database named A, its fields are Month, Year, Amount.
November month Total Amount=10000
December " =20000
January " =00000
I select November month with 2007 yr, it is showing 10000. Again I select December 2007, it is showing 20000, properly. But if I select January 2007, it is showing 20000 (the previous data).
I am giving you code...

I have declared in module...
Global con as new ADODB.connection

General declaration------

Dim data1 As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim subs As String
Dim subs1 As String

clik a command button()

On Error Resume Next
data1.ActiveConnection = con
data1.CommandText = "select sum(amunt) from amn where mon & yr & schm='" & (Combo1.Text) & "' & '" & (Text1.Text) & "''"
rs.Open data1, , adOpenDynamic, adLockReadOnly
If Not rs.EOF Then
  subs = rs.Fields(0)  /data field
  subs = rs.Fields(1)  / ''
  subs = rs.Fields(2)
  subs = rs.Fields(3)
  Label9.Caption = subs
End If
rs.Close

Please help me.

[Killer42]

If you're going to post code here, you really should paste it directly so that we know we're looking at what actually executes. We can't trust the code you have posted here, because it has things which obviously wouldn't work. For example:

• Field name amunt in the SQL doesn't match what you told us.
• The variable subs is overwritten multiple times - doesn't make any sense.
• Does that "where mon & yr ..." in the SQL work? I'm not familiar with the syntax.

[mesubham]

I am clearingly you properly.

The backend of my project is MS Access... Where a table named amn is being created, where its fields name are amunt, mon, yr, schm.
In this VB form, I have to select the Month(i.e. mon), Year(i.e yr ), Scheme(i.e schm).

I'm sorry, the sub will be used only 1 time i.e subs = rs.Fields(0).

This above code is working properly but when I want to see the transacted less month total amount after a transacted amount displays, it did not change.

On Error Resume Next
data1.ActiveConnection = con
data1.CommandText = "select sum(amunt) from amn where mon & yr & schm='" & (Combo1.Text) & "' & '" & (Text1.Text) & "''"
rs.Open data1, , adOpenDynamic, adLockReadOnly
If Not rs.EOF Then
  subs = rs.Fields(0)  /data field
  Label9.Caption = subs
End If
rs.Close

[Killer42]

I am clearingly you properly.

Um... you are what?!


Anyway, it sounds as though for the months that are giving you trouble, there simply aren't any records. So one of two things is probably happening. Either the On Error Resume Next is skipping over part of your code (test this by commenting it out) or the If Not rs.EOF Then test is not firing because EOF is True.

[mesubham]

It is working. I have added a code before rs.close. The code is.
subs=label4.caption=""
then it is changing but only showing False statement. I want a value i.e 0.00. How can I do this..

updated code.......

  On Error Resume Next
  data1.ActiveConnection = con
  data1.CommandText = "select sum(amunt) from amn where mon & yr &      schm='" & (Combo1.Text) & "' & '" & (Text1.Text) & "''"
  rs.Open data1, , adOpenDynamic, adLockReadOnly
  If Not rs.EOF Then
    subs = rs.Fields(0)  /data field
    Label9.Caption = subs
  End If
  subs=label4.caption=""    /I have added this
  rs.Close

Please let me know.

[Killer42]

Um... Ok, got it!

It looks as though a little VB syntax lesson is in order.

Can you tell me what you think this line does?

subs = label4.caption = ""

Also, I have another question. Does "/" indicate a comment? If you are going to post code, please stick to actual valid code, so people can test it.