13,054,329 members (95,318 online)
Rate this:
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:-
```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
ridoy59.3K
v3
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 ?
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?
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)

Rate this:

## 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
Rate this:

## 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

Top Experts
Last 24hrsThis month
 OriginalGriff 175 Graeme_Grant 168 Jochen Arndt 135 Dave Kreskowiak 85 RickZeeland 60
 OriginalGriff 5,731 RickZeeland 2,014 ppolymorphe 1,858 F-ES Sitecore 1,646 Dave Kreskowiak 1,494