sum() function is not returning value

[gillian3114]

 
why this sum function won't work ? 
eg Sum(personal)
lblPersonal.Caption it wont retrieve the sum value of the personal field
 
Dim cn6 As New ADODB.Connection
Dim strCNString4 As String
Dim rs6 As New ADODB.Recordset
Dim intFound6 As Double
 
strCNString6 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
cn6.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn6.ConnectionString = strCNString6
 
cn6.Open
 
    With rs6
        '.Open "SELECT Sum(personal), Sum(sick), Sum(vacation), Sum(maternity), emp_id, from_date, to_date FROM leavela WHERE Month(from_date)=" & intMonth & " and Year(from_date)=" & intYear & " and Month(to_date)=" & intMonth & " and Year(to_date)=" & intYear & " GROUP BY emp_id, from_date, to_date ", cn6, adOpenDynamic, adLockOptimistic
 
         Do Until .EOF
 
             If !emp_id = txtEmp_ID.Text Then
                   lblPersonal.Caption = rs6.Fields(0) 'here
                   lblSick.Caption = rs6.Fields(1) 'here
                   Vacation.Caption = rs6.Fields(2) 'here
                   lblMaternity.Caption = rs6.Fields(3) 'here
 
                    intFound6 = 1
             End If
 
            .MoveNext
          Loop
 
          'If intFound6 = 0 Then
                   'MsgBox "Employee ID not found !", vbExclamation, "Error"
          'End If
 
     End With
 
 

[ADezii]

 
why this sum function won't work ? 
eg Sum(personal)
lblPersonal.Caption it wont retrieve the sum value of the personal field
 
Dim cn6 As New ADODB.Connection
Dim strCNString4 As String
Dim rs6 As New ADODB.Recordset
Dim intFound6 As Double
 
strCNString6 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
cn6.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn6.ConnectionString = strCNString6
 
cn6.Open
 
    With rs6
        '.Open "SELECT Sum(personal), Sum(sick), Sum(vacation), Sum(maternity), emp_id, from_date, to_date FROM leavela WHERE Month(from_date)=" & intMonth & " and Year(from_date)=" & intYear & " and Month(to_date)=" & intMonth & " and Year(to_date)=" & intYear & " GROUP BY emp_id, from_date, to_date ", cn6, adOpenDynamic, adLockOptimistic
 
         Do Until .EOF
 
             If !emp_id = txtEmp_ID.Text Then
                   lblPersonal.Caption = rs6.Fields(0) 'here
                   lblSick.Caption = rs6.Fields(1) 'here
                   Vacation.Caption = rs6.Fields(2) 'here
                   lblMaternity.Caption = rs6.Fields(3) 'here
 
                    intFound6 = 1
             End If
 
            .MoveNext
          Loop
 
          'If intFound6 = 0 Then
                   'MsgBox "Employee ID not found !", vbExclamation, "Error"
          'End If
 
     End With
 
 

1. Are the sure the Recordset is returning any Records?
2. Is the following condition ever being met? (!emp_id = txtEmp_ID.Text )
3. Change the syntax of the following line of code:
   

   !emp_id = txtEmp_ID.Text ==> !emp_id = Me![txtEmp_ID]
   NOTE: The Text property is only valid when the Field has the focus.

[nico5038]

Best to try the query first in the query editor. (Use a breakpoint and F8 to step through the code and type in the immediate window a "?" followed by the stringing to get the result in textmode for a copy/paste in the query editor.)

Next it's better to use:
Sum(NZ(personal))
as Null values can spoil the result.

Nic;o)