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:
   [CODE=vb]!emp_id = txtEmp_ID.Text ==> !emp_id = Me![txtEmp_ID]
   NOTE: The Text property is only valid when the Field has the focus.[/CODE]

[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)