<a href="https://bytes.com/topic/visual-basic/answers/624914-sum-access-function-help">sum() access function help....

375

256MB

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

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, adLockOptimistic

Check out and let me know

Mar 29 '07 #2

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

  
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

Mar 30 '07 #3

pureenhanoi

175

100+

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

 
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

Mar 30 '07 #4

pureenhanoi

175

100+

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

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

375

256MB

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

375

256MB

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

thanks cmrhema , that really work ^_^

Mar 30 '07 #9

Expand|Select|Wrap|Line Numbers

  
  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

Mar 30 '07 #10

How to sum the textboxs in a report

375

256MB

Expand|Select|Wrap|Line Numbers

  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

Mar 30 '07 #11

Similar topics

by: kai | last post by:

Hi, All I use Access2002. I have a report, on each group I have a subtotal (not directly from fields, get from on report calculation) with name txtSubTotal. At report level, I want sum up...

Multiple Table Query using Sum Function

by: Scott Cannon | last post by:

I am trying to query 3 tables all related by Clinet_ID. The Clients table, Monthly_Expenses table and Monthly_Income table. Each client can have 0>M instances of expenses, past due expenses, and...

the SUM function and rounding?

by: jwa6 | last post by:

I have a field on a form using the sum function made by this formula = Sum() format is 0000.00 this 'sum' seems to leave out cents. by that I mean if I have 3 values in ()

Cumulative Sum in query is very slow...

by: cefrancke | last post by:

Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?). For example, I want to sum up all values under...

Subquery Sum by Date Range

by: cincyn12 | last post by:

Hi, I'm really new to access. (Am only a beginner in SQL) I'm trying to create subqueries similar to SQL+ for a sum using case when within date ranges. (Or Decode by month sum) i.e. Trying to...

Sum IF in Access

by: Tee GEE | last post by:

Is there a SumIF function in Access? I have to SUM a duration of time, but only if a logical Yes/No check box is TRUE. I am working with the expression builder and a Text Box. Thanks for the...

Running Sum

by: BrianMiller | last post by:

I have searched for an answer but have failed to come up with anything that seems to work. I am trying to set up an Access 2003 database for home accounts. I want to create a running sum (a...

Sum Time Function.

by: dwasbig9 | last post by:

Hi Group (fairly limited knowledge of Access and almost none of Access VBA. Using Access 2003). I need to sum time, I've found through the groups archive an sql extract that led me to this ...

Wordpress or something else?

max(), sum(), next()

by: bearophileHUGS | last post by:

Empty Python lists don't know the type of the items it will contain, so this sounds strange: 0 Because that may be an empty sequence of someobject: 0 In a statically typed language in...

Python

by: Faith0G | last post by:

I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

Content Management Systems

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

Batch import of multiple excel files into the database

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?