By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,097 Members | 1,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,097 IT Pros & Developers. It's quick & easy.

sum() access function help....

P: 16
now i need is select that specify month and show that sum of total_hr.
eg:

working_date total_hr total_ot_hr
20/2/2007 5 10
21/2/2007 5 10
22/2/2007 5 10
23/2/2007 5 10
23/3/2007 5 10

if i select febuary it will :
retrieve sum of total_hr = 20
retrieve sum of total_ot_hr = 40

pls help me.

thanks !

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim cn3 As New ADODB.Connection
  3. Dim strCNString3 As String
  4. Dim rs3 As New ADODB.Recordset
  5. Dim intFound3 As Double
  6.  
  7. strCNString3 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
  8. cn3.Provider = "Microsoft Jet 4.0 OLE DB Provider"
  9. cn3.ConnectionString = strCNString3
  10.  
  11. cn3.Open
  12.  
  13. Dim intMonth As Integer
  14.  
  15. If cboMonth.ListIndex = 0 Then
  16.     intMonth = 0
  17. ElseIf cboMonth.ListIndex = 1 Then
  18.     intMonth = 1
  19. ElseIf cboMonth.ListIndex = 2 Then
  20.     intMonth = 2
  21. ElseIf cboMonth.ListIndex = 3 Then
  22.     intMonth = 3
  23. ElseIf cboMonth.ListIndex = 4 Then
  24.     intMonth = 4
  25. ElseIf cboMonth.ListIndex = 5 Then
  26.     intMonth = 5
  27. ElseIf cboMonth.ListIndex = 6 Then
  28.     intMonth = 6
  29. ElseIf cboMonth.ListIndex = 7 Then
  30.     intMonth = 7
  31. ElseIf cboMonth.ListIndex = 8 Then
  32.     intMonth = 8
  33. ElseIf cboMonth.ListIndex = 9 Then
  34.     intMonth = 9
  35. ElseIf cboMonth.ListIndex = 10 Then
  36.     intMonth = 10
  37. ElseIf cboMonth.ListIndex = 11 Then
  38.     intMonth = 11
  39. End If
  40.  
  41.   With rs3
  42.  
  43.         .Open "SELECT working_date, Sum(total_hr), Sum(total_ot_hr) FROM clock GROUP BY working_date", cn3, adOpenDynamic, adLockOptimistic
  44.          Do Until .EOF
  45.  
  46.                  If Month(!working_date) = intMonth Then  'why this not effect
  47.  
  48.                     txtWorkedHr.Text = !total_hr   'here is i wan the total of sum
  49.                     txtWorkedOtHr.Text = !total_ot_hr  'here is i wan the total of sum
  50.                     intFound3 = 1
  51.                  End If
  52.  
  53.             .MoveNext
  54.           Loop
  55.  
  56.      End With
  57.  
  58.  
Mar 29 '07 #1
Share this Question
Share on Google+
10 Replies


100+
P: 375
now i need is select that specify month and show that sum of total_hr.
eg:

working_date total_hr total_ot_hr
20/2/2007 5 10
21/2/2007 5 10
22/2/2007 5 10
23/2/2007 5 10
23/3/2007 5 10

if i select febuary it will :
retrieve sum of total_hr = 20
retrieve sum of total_ot_hr = 40

pls help me.

thanks !

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim cn3 As New ADODB.Connection
  3. Dim strCNString3 As String
  4. Dim rs3 As New ADODB.Recordset
  5. Dim intFound3 As Double
  6.  
  7. strCNString3 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
  8. cn3.Provider = "Microsoft Jet 4.0 OLE DB Provider"
  9. cn3.ConnectionString = strCNString3
  10.  
  11. cn3.Open
  12.  
  13. Dim intMonth As Integer
  14.  
  15.  
  16.  
  17.  
  18.   With rs3
  19.  
  20.         .Open "SELECT working_date, Sum(total_hr), Sum(total_ot_hr) FROM clock GROUP BY working_date", cn3, adOpenDynamic, adLockOptimistic
  21.  
  22.  
Try out this first
--
Dim FirstDate As String 'Declare variables.
Dim intMonth as integer
Dim SecondDate As Date
FirstDate = InputBox("Enter a date:")
SecondDate = CDate(FirstDate) ' give as 12-30-2006
intMonth = DatePart("m", secondate)
MsgBox (intMonth) 'This will result in 12
----

PUT DOWN THE QUERY AS
.Open "SELECT working_date, Sum(total_hr), Sum(total_ot_hr) FROM clock WHERE datepart(month,working_date)=" intMonth, cn3, adOpenDynamic, adLockOptimistic



Check out and let me know
Mar 29 '07 #2

P: 16
i already change . now got error is:
No value given for one or more required parameters

if i no give group by working_date it will pop up a error.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim cn3 As New ADODB.Connection
  3. Dim strCNString3 As String
  4. Dim rs3 As New ADODB.Recordset
  5. Dim intFound3 As Integer
  6.  
  7. strCNString3 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
  8. cn3.Provider = "Microsoft Jet 4.0 OLE DB Provider"
  9. cn3.ConnectionString = strCNString3
  10.  
  11. cn3.Open
  12.  
  13. Dim FirstDate As String 'Declare variables.
  14. Dim intMonth As Integer
  15. Dim SecondDate As Date
  16. FirstDate = InputBox("Enter a date:")
  17. SecondDate = CDate(FirstDate) ' give as 12-30-2006
  18. intMonth = DatePart("m", SecondDate)
  19. MsgBox (intMonth) 'This will result in 12
  20.  
  21.  
  22. 'im using combo box to choose the month.
  23.  
  24. If cboMonth.ListIndex = 0 Then
  25.     intMonth = 0
  26. ElseIf cboMonth.ListIndex = 1 Then
  27.     intMonth = 1
  28. ElseIf cboMonth.ListIndex = 2 Then
  29.     intMonth = 2
  30. ElseIf cboMonth.ListIndex = 3 Then
  31.     intMonth = 3
  32. ElseIf cboMonth.ListIndex = 4 Then
  33.     intMonth = 4
  34. ElseIf cboMonth.ListIndex = 5 Then
  35.     intMonth = 5
  36. ElseIf cboMonth.ListIndex = 6 Then
  37.     intMonth = 6
  38. ElseIf cboMonth.ListIndex = 7 Then
  39.     intMonth = 7
  40. ElseIf cboMonth.ListIndex = 8 Then
  41.     intMonth = 8
  42. ElseIf cboMonth.ListIndex = 9 Then
  43.     intMonth = 9
  44. ElseIf cboMonth.ListIndex = 10 Then
  45.     intMonth = 10
  46. ElseIf cboMonth.ListIndex = 11 Then
  47.     intMonth = 11
  48. End If
  49.  
  50.   With rs3
  51. 'error occur here:
  52.         .Open "SELECT working_date, Sum(total_hr), Sum(total_ot_hr) FROM clock WHERE datepart(month,working_date)=" & intMonth & " GROUP BY working_date", cn3, adOpenDynamic, adLockOptimistic
  53.  
  54.  
  55.  
  56.          Do Until .EOF
  57.  
  58.                     txtWorkedHr.Text = !total_hr
  59.                     txtWorkedOtHr.Text = !total_ot_hr
  60.                     intFound3 = 1
  61.  
  62.  
  63.  
  64.  
  65.             .MoveNext
  66.           Loop
  67.  
  68.           If intFound3 = 0 Then
  69.                    MsgBox "Employee ID not found !", vbExclamation, "Search"
  70.           End If
  71.  
  72.      End With
  73.  
  74.  
  75.  
Mar 30 '07 #3

pureenhanoi
100+
P: 175
i already change . now got error is:
No value given for one or more required parameters

if i no give group by working_date it will pop up a error.
This error cause by at least one column name is invalid. You must use exactly column name in your table to create query.

I have a small idea to change your code. By using ItemData property of ComboBox (OR ListBox) you can save more time to code
Expand|Select|Wrap|Line Numbers
  1. Private sub Form_Load()
  2. dim i as long
  3. for i = 0 to cboMonth.ListCount-1
  4.     cboMonth.ItemData(i) = i
  5. Next
  6. End sub
  7.  
  8. private sub command1_Click()
  9.    monthIndex  = cboMonth.ItemData(cboMonth.ListIndex)
  10.   'do your query here
  11. End sub
  12.  
Mar 30 '07 #4

pureenhanoi
100+
P: 175
wait for a minutes, i think you're wrong at this possition
the DatePart() function need first param is a string.
so you must do like this:
WHERE DatePart('Month',Date) =
use "Month" in common call of DatePart and 'Month' in a query
Mar 30 '07 #5

P: 16
With rs3

.Open "SELECT emp_id, working_date, Sum(total_hr), Sum(total_ot_hr) FROM clock WHERE Month(working_date)=" & intMonth & " and Year(working_date)=" & intYear & " and emp_id= ' " & txtEmp_ID.Text & " ' GROUP BY working_date, emp_id", cn3, adOpenDynamic, adLockOptimistic
'Do Until .EOF

txtWorkedHr.Text = ? ' what should i put here for value of sum total_hr ?
txtWorkedOtHr.Text = ? ' what should i put here for value of sum total_ot_hr ?


'.MoveNext
'Loop

End With
Mar 30 '07 #6

100+
P: 375
TRY OUT THIS AND REPLY


Dim cn3 As New ADODB.Connection
Dim strCNString3 As String
Dim rs3 As New ADODB.Recordset
Dim intFound3 As Double

strCNString3 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
cn3.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn3.ConnectionString = strCNString3

cn3.Open

Dim intMonth As Integer
'Here CHANGE the index 0 and start from 1 to 12. Otherwise you will not get value for december month
If cboMonth.ListIndex = 0 Then
intMonth = 0
ElseIf cboMonth.ListIndex = 1 Then
intMonth = 1
ElseIf cboMonth.ListIndex = 2 Then
intMonth = 2
ElseIf cboMonth.ListIndex = 3 Then
intMonth = 3
ElseIf cboMonth.ListIndex = 4 Then
intMonth = 4
ElseIf cboMonth.ListIndex = 5 Then
intMonth = 5
ElseIf cboMonth.ListIndex = 6 Then
intMonth = 6
ElseIf cboMonth.ListIndex = 7 Then
intMonth = 7
ElseIf cboMonth.ListIndex = 8 Then
intMonth = 8
ElseIf cboMonth.ListIndex = 9 Then
intMonth = 9
ElseIf cboMonth.ListIndex = 10 Then
intMonth = 10
ElseIf cboMonth.ListIndex = 11 Then
intMonth = 11
End If

With rs3
'You want only the sum of total_hr and sum of total_ot_hr
.Open "SELECT Sum(total_hr), Sum(total_ot_hr) FROM
clock WHERE datepart(month,working_date)=" intMonth, cn3, adOpenDynamic, adLockOptimistic


txtWorkedHr.Text = rs3.Fields(0) 'here is i wan the total of sum
txtWorkedOtHr.Text = rs3.Fields(1) 'here is i wan the total of sum
intFound3 = 1
Mar 30 '07 #7

100+
P: 375
TRY OUT THIS AND REPLY


Dim cn3 As New ADODB.Connection
Dim strCNString3 As String
Dim rs3 As New ADODB.Recordset
Dim intFound3 As Double

strCNString3 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
cn3.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn3.ConnectionString = strCNString3

cn3.Open

Dim intMonth As Integer
'Here CHANGE the index 0 and start from 1 to 12. Otherwise you will not get value for december month
If cboMonth.ListIndex = 0 Then
intMonth = 0
ElseIf cboMonth.ListIndex = 1 Then
intMonth = 1
ElseIf cboMonth.ListIndex = 2 Then
intMonth = 2
ElseIf cboMonth.ListIndex = 3 Then
intMonth = 3
ElseIf cboMonth.ListIndex = 4 Then
intMonth = 4
ElseIf cboMonth.ListIndex = 5 Then
intMonth = 5
ElseIf cboMonth.ListIndex = 6 Then
intMonth = 6
ElseIf cboMonth.ListIndex = 7 Then
intMonth = 7
ElseIf cboMonth.ListIndex = 8 Then
intMonth = 8
ElseIf cboMonth.ListIndex = 9 Then
intMonth = 9
ElseIf cboMonth.ListIndex = 10 Then
intMonth = 10
ElseIf cboMonth.ListIndex = 11 Then
intMonth = 11
End If

With rs3
'You want only the sum of total_hr and sum of total_ot_hr
.Open "SELECT Sum(total_hr), Sum(total_ot_hr) FROM
clock WHERE datepart(month,working_date)=" intMonth, cn3, adOpenDynamic, adLockOptimistic


txtWorkedHr.Text = rs3.Fields(0) 'here is i wan the total of sum
txtWorkedOtHr.Text = rs3.Fields(1) 'here is i wan the total of sum
intFound3 = 1

Wait for my next post before trying out this code
The above code works if the backend is SQL SERVER
Mar 30 '07 #8

P: 16
thanks cmrhema , that really work ^_^
Mar 30 '07 #9

P: 16
Expand|Select|Wrap|Line Numbers
  1.  
  2.   With rs3
  3.  
  4.         .Open "SELECT Sum(total_hr), Sum(total_ot_hr), emp_id FROM clock WHERE Month(working_date)=" & intMonth & " and Year(working_date)=" & intYear & "  and emp_id = ' " & txtEmp_ID.Text & " ' GROUP BY emp_id ", cn3, adOpenDynamic, adLockOptimistic
  5.         Do Until .EOF
  6.         ' why this cant show that sum value.
  7.        ' if dint put emp_id it will work
  8.                     txtWorkedHr.Text = rs3.Fields(0)
  9.                     txtWorkedOtHr.Text = rs3.Fields(1)
  10.  
  11.  
  12.         .MoveNext
  13.         Loop
  14.  
  15.      End With
  16.  
  17.  
Mar 30 '07 #10

100+
P: 375
Expand|Select|Wrap|Line Numbers
  1.  
  2.   With rs3
  3.  
  4.         .Open "SELECT Sum(total_hr), Sum(total_ot_hr), emp_id FROM clock WHERE Month(working_date)=" & intMonth & " and Year(working_date)=" & intYear & "  and emp_id = ' " & txtEmp_ID.Text & " ' GROUP BY emp_id ", cn3, adOpenDynamic, adLockOptimistic
  5.         Do Until .EOF
  6.         ' why this cant show that sum value.
  7.        ' if dint put emp_id it will work
  8.                     txtWorkedHr.Text = rs3.Fields(0)
  9.                     txtWorkedOtHr.Text = rs3.Fields(1)
  10.  
  11.  
  12.         .MoveNext
  13.         Loop
  14.  
  15.      End With
  16.  
  17.  
Whenever you are using a sum or average or such functions in a query you are NOT supposed to include other fields. So remove empid in the select statement . Moroever I think you can get away with the group by part
Mar 30 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.