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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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