sum() function is not returning value | Newbie | | Join Date: Mar 2007
Posts: 16
| | -
-
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
-
-
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: sum() function is not returning value
Quote:
Originally Posted by 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
-
-
- Are the sure the Recordset is returning any Records?
- Is the following condition ever being met? (!emp_id = txtEmp_ID.Text )
- 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.
|  | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,232
| | | re: sum() function is not returning value
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)
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,439 network members.
|
