Click here to Skip to main content
12,457,187 members (55,666 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB
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:-
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
Updated 3-Dec-12 0:35am
ridoy58.6K
v3
Comments
VitorHugoGarcia 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?
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Dear all
I have found the solution to the problem:

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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160826.1 | Last Updated 3 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100