sum of two MS Access database fields

Question

1.00/5 (1 vote)

See more:

XML

Friends the problem for calculating the sum of two fields is resolved. Thanks to you all but there is one more problem in that:
actually i need to display the sum of these two fields(refund and sub) on a VB6 data report using MS Access database. If there is only one record based on the cretaria (select * from employees where Designation='" &amp; cmbodesig.Text &amp; "') then it is displaying the sum but if there are two or more employees with a particular designation it loops through all but displays the total sum on the report which is creating a problem.
I want like
name     designation   ref   sub   total(sum of ref+sub of each employee)

and under these columns i want the individual records
The code which i am trying is
<code>
rs.MoveFirst
Dim cc, xx, tt As Long
  cc = rs.Fields(16)
  xx = rs.Fields(17)
tt = cc + xx
Do While Not rs.EOF

 DataReport2.Sections("Section1").Controls("label16").Caption = tt
 rs.MoveNext
 Loop
</code>
please help

Thank you

Sarfaraz

Posted 25-Jun-13 8:23am

sarfarazbhat

Updated 28-Jun-13 16:07pm

v4

Add a Solution

Comments

Maciej Los 25-Jun-13 16:31pm

Is it MS Access report?

sarfarazbhat 25-Jun-13 22:57pm

Yes sir, MS Access as back end with VB 6.0 as front end

Maciej Los 26-Jun-13 6:02am

What is DataReport1? CrystalReport, ReportViewer?
What you mean VB: VB6 or VB.NET?
Comparison of Visual Basic and Visual Basic .NET[^]

sarfarazbhat 26-Jun-13 9:00am

Sir it is visual basic 6.0 inbuilt data report not crystal reports and it is vb6 not vb.net and database is Ms access.

Ron Beyer 27-Jun-13 10:20am

Can you please post the code that you use to add the two fields?

Jason Gleim 27-Jun-13 10:24am

What is the actual sum of the two fields? Does it exceed the limit of the int or double you are trying to put it into to? Are you doing something silly like trying to add the values of two string columns without converting them to int or doubles first?

kpolecastro 27-Jun-13 10:49am

Can you sum the columns individually? Sum of Column A is... and Sum of Column B is ... then just add the two together? or are you trying to sum the two columns per record? do any of your records contain a null value or nothing in any of the columns you want to sum?

ZurdoDev 27-Jun-13 10:36am

You need to post the relevant code.

1 solution

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 · 2013-06-27T04:45:00

Solution 1

Hello friends
I find the solution by looking into my code
the code that worked is as

<br />
Set rs = New ADODB.Recordset<br />
rs.Open "select * from employees where Designation='" & cmbodesig.Text & "'", Con, adOpenDynamic, adLockPessimistic<br />
Dim cc, xx, tt As Integer<br />
  cc = rs.Fields(16)<br />
  xx = rs.Fields(17)<br />
tt = cc + xx<br />
msgbox tt<br />
<br />

Thank you all esp. Ron Beyer and Maciej Los for their involvement into my question
Thank you

Posted 27-Jun-13 4:45am

sarfarazbhat

Updated 27-Jun-13 4:46am

v3

Comments

sarfarazbhat 28-Jun-13 9:56am

Friends the problem for calculating the sum of two fields is resolved. Thanks to you all but there is one more problem in that:
actually i need to display the sum of these two fields(refund and sub) on a VB6 data report using MS Access database. If there is only one record based on the cretaria (select * from employees where Designation='" & cmbodesig.Text & "') then it is displaying the sum but if there are two or more employees with a particular designation it loops through all but displays the total sum on the report which is creating a problem.
I want like
name designation ref sub total(sum of ref+sub of each employee)

and under these columns i want the individual records
The code which i am trying is


rs.MoveFirst
Dim cc, xx, tt As Long
  cc = rs.Fields(16)
  xx = rs.Fields(17)
tt = cc + xx
Do While Not rs.EOF

 DataReport2.Sections("Section1").Controls("label16").Caption = tt
 rs.MoveNext
 Loop

please help