Calculate sum of a field!

Question

0.00/5 (No votes)

See more:

Dear all
Actually i am printing a report by selecting a DATE and the report is being printed for all the records on that DATE.
Now i have a field RATE in my table and i want to calculate the sum of that RATE field based on the criteria (on that date).
My report is being printed without any problem using the below code:-

VB

rs.Open "select * from patients where Receipt_date=#" & Label3.Caption & "#", Con, adOpenDynamic, adLockPessimistic
       Set DataReport1.DataSource = rs
       DataReport1.Show

but i want to calculate the sum of that RATE field based on the criteria (on that date)

i want to put the total sum on a datareport.

Thank you

Posted 2-Dec-12 20:53pm

sarfarazbhat

Updated 3-Dec-12 0:35am

ridoy

v3

Add a Solution

Comments

_Vitor Garcia_ 3-Dec-12 4:29am

What is happening and what do you expected to ?
What is the datatype of Rate Field ?
Does this throw any exception ?

sarfarazbhat 3-Dec-12 6:28am

Actually i am printing a report by selecting a date and the report is being printed for all the records on that date.
Now i have a field Rate of items and i want to calculate the sum of that rate field based on the criteria (on that date).
My report is being printed without any problem using the below code:-
rs.Open "select * from patients where Receipt_date=#" & Label3.Caption & "#", Con, adOpenDynamic, adLockPessimistic
Set DataReport1.DataSource = rs
DataReport1.Show
but i want to calculate the sum of that rate field based on the criteria (on that date)

E.F. Nijboer 3-Dec-12 4:43am

Why not use SQL SUM directly? Using magic numbers is very bad practice -> rs.fields(5), what if the table changes?

2 solutions

Solution 2

Dim x As Integer
Do While Not rs.EOF

x = x + rs.Fields(6).Value

rs.MoveNext Loop

DataReport1.Sections("Section5").Controls("label17").C aption = x Set DataReport1.DataSource = rs DataReport1.Show

Posted 3-Dec-12 14:36pm

sarfarazbhat

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sarfarazbhat · Accepted Answer · 2012-12-03T02:24:00

Dear all
I have found the solution to the problem:

VB

Dim x As Integer
Do While Not rs.EOF

  x = x + rs.Fields(6).Value

  rs.MoveNext
Loop

   DataReport1.Sections("Section5").Controls("label17").Caption = x
        Set DataReport1.DataSource = rs
        DataReport1.Show

It solved the problem.
Sarfaraz

Calculate sum of a field!

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Calculate sum of a field!

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us