Friends,

I can get the total of whole field but what I want to know is how can I get the total of a result after querry shows the data?

What I mean is: Suppose there are 4 rows and 2 customers (I have 100 customers in my table. Just this is an example.):

1) Sneha Plastics  2) Nirmala Plastics

Sneha Plastics    100
Nirmala Plastics  200
Sneha Plastics    300
Nirmala Plastics  400

The total amount for both customers are 1,000.

Now I want to view the customer Sneha's details. So I use the querry and got the following details:

Sneha Plastics    100
Sneha Plastics    300

How to get this total in a text box?

Could somebody help me?

Regards.


____________________________
Sneha

Hi sneha,
I am assuming that you have fetched your query results into a Recordset object, so all you have to do is Loop through the Recordset adding the quantities to a variable.

Dim lngTotal As Long lngTotal = 0 rstCustomers.MoveFirst Do Until rstCustomers.EOF lngTotal = lngTotal + rstCustomers("Quantity") rstCustomers.MoveNext Loop Me.TextBoxTotal = lngTotal

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hi GeoffS,

Really it works nicely. Thanks a lot.

One more querry I have and hope you will help me.

rs.Open "Select [Date], Debit, Credit, (Select Sum (Debit-Credit),From Accounts b Where b.[Date]>=a.[Date] From Accounts a Order By a.[Date]", db, adOpenDynamic, adLockOptimistic

Actually I need the above code for the following attached project.


[Edited by sneha on 29-03-2006 at 12:57 AM GMT]

[Edited by sneha on 29-03-2006 at 12:44 PM GMT]

____________________________
Sneha

____________________________
Attached:
Accounts.zip 12 KB (Downloads: 4)

Hi Sneha,

I couldn't open your zip file, it seems to be corrupted, so I am guessing that what you are asking is for the SQL Query to be re-written from T-SQL to standard SQL. If so then I would first make the observation that to name a database field "Date" is not a good idea, any Field name that can be interpreted as an inbuilt Function or a "Keyword" should really be avoided so as to save confusion - if those square brackets get omitted by mistake then you are going to get problems. I would suggest either prefixing it with the data type ("dteDate") or if you want to keep it more readable maybe "transaction_date".

Can you try posting your zip file again so that I can see what you need.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

"Select [Date], Debit, Credit, (Select Sum (Debit-Credit) From Accounts b Where b.[Date]>=a.[Date]) From Accounts a Order By a.[Date]"

____________________________
If you find the answer helpful, please mark this topic as solved.