sum() access function help....

**cmrhema** · Mar 29 '07, 04:08 PM

Originally posted by gillian3114

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 !

Code:

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




  With rs3

        .Open "SELECT working_date, Sum(total_hr), Sum(total_ot_hr) FROM clock GROUP BY working_date", cn3, adOpenDynamic, adLockOptimistic

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, adLockOptimisti c

Check out and let me know

**gillian3114** · Mar 30 '07, 01:34 AM

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.

Code:

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

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

cn3.Open

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", SecondDate)
MsgBox (intMonth) 'This will result in 12


'im using combo box to choose the 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
'error occur here:
        .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



         Do Until .EOF
                 
                    txtWorkedHr.Text = !total_hr
                    txtWorkedOtHr.Text = !total_ot_hr
                    intFound3 = 1
                 
                    
                    
            
            .MoveNext
          Loop
          
          If intFound3 = 0 Then
                   MsgBox "Employee ID not found !", vbExclamation, "Search"
          End If
          
     End With

**pureenhanoi** · Mar 30 '07, 04:40 AM

Originally posted by gillian3114

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

Code:

Private sub Form_Load()
dim i as long
for i = 0 to cboMonth.ListCount-1
    cboMonth.ItemData(i) = i
Next
End sub

private sub command1_Click()
   monthIndex  = cboMonth.ItemData(cboMonth.ListIndex)
  'do your query here
End sub

**pureenhanoi** · Mar 30 '07, 04:53 AM

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

**gillian3114** · Mar 30 '07, 07:07 AM

With rs3

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

txtWorkedHr.Tex t = ? ' what should i put here for value of sum total_hr ?
txtWorkedOtHr.T ext = ? ' what should i put here for value of sum total_ot_hr ?

'.MoveNext
'Loop

End With

**cmrhema** · Mar 30 '07, 09:51 AM

TRY OUT THIS AND REPLY

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

strCNString3 = "Data Source=" & App.Path & "\PayrollBakery DB.mdb"
cn3.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn3.ConnectionS tring = 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.ListIn dex = 0 Then
intMonth = 0
ElseIf cboMonth.ListIn dex = 1 Then
intMonth = 1
ElseIf cboMonth.ListIn dex = 2 Then
intMonth = 2
ElseIf cboMonth.ListIn dex = 3 Then
intMonth = 3
ElseIf cboMonth.ListIn dex = 4 Then
intMonth = 4
ElseIf cboMonth.ListIn dex = 5 Then
intMonth = 5
ElseIf cboMonth.ListIn dex = 6 Then
intMonth = 6
ElseIf cboMonth.ListIn dex = 7 Then
intMonth = 7
ElseIf cboMonth.ListIn dex = 8 Then
intMonth = 8
ElseIf cboMonth.ListIn dex = 9 Then
intMonth = 9
ElseIf cboMonth.ListIn dex = 10 Then
intMonth = 10
ElseIf cboMonth.ListIn dex = 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, adLockOptimisti c

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

**cmrhema** · Mar 30 '07, 10:15 AM

Originally posted by cmrhema

TRY OUT THIS AND REPLY

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

strCNString3 = "Data Source=" & App.Path & "\PayrollBakery DB.mdb"
cn3.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn3.ConnectionS tring = 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.ListIn dex = 0 Then
intMonth = 0
ElseIf cboMonth.ListIn dex = 1 Then
intMonth = 1
ElseIf cboMonth.ListIn dex = 2 Then
intMonth = 2
ElseIf cboMonth.ListIn dex = 3 Then
intMonth = 3
ElseIf cboMonth.ListIn dex = 4 Then
intMonth = 4
ElseIf cboMonth.ListIn dex = 5 Then
intMonth = 5
ElseIf cboMonth.ListIn dex = 6 Then
intMonth = 6
ElseIf cboMonth.ListIn dex = 7 Then
intMonth = 7
ElseIf cboMonth.ListIn dex = 8 Then
intMonth = 8
ElseIf cboMonth.ListIn dex = 9 Then
intMonth = 9
ElseIf cboMonth.ListIn dex = 10 Then
intMonth = 10
ElseIf cboMonth.ListIn dex = 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, adLockOptimisti c

txtWorkedHr.Tex t = rs3.Fields(0) 'here is i wan the total of sum
txtWorkedOtHr.T ext = 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

**gillian3114** · Mar 30 '07, 11:47 AM

thanks cmrhema , that really work ^_^

**gillian3114** · Mar 30 '07, 12:52 PM

Code:

  With rs3

        .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
        Do Until .EOF
        ' why this cant show that sum value.
       ' if dint put emp_id it will work
                    txtWorkedHr.Text = rs3.Fields(0)
                    txtWorkedOtHr.Text = rs3.Fields(1)
                    
                 
        .MoveNext
        Loop
          
     End With

**cmrhema** · Mar 30 '07, 02:34 PM

Originally posted by gillian3114

Code:

  With rs3

        .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
        Do Until .EOF
        ' why this cant show that sum value.
       ' if dint put emp_id it will work
                    txtWorkedHr.Text = rs3.Fields(0)
                    txtWorkedOtHr.Text = rs3.Fields(1)
                    
                 
        .MoveNext
        Loop
          
     End With

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

sum() access function help....

sum() access function help....

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment