DataGrids and Calculations

Posted on 2006-04-25
Last Modified: 2008-02-01
Here is a tough one.  I have some data that is going into a datagrid via DATASETS.  I need to Grab 3 totals.
The final total I already have using this code.
        Dim x
        If DsOtherInc1.tblotherinc.Rows.Count > 0 Then
            x = dgotherinc.DataSource.compute("SUM([Brw Amount])", 1 = 1)
            txtTTLOthInc.Text = x
        End If
The other 2 totals are based off a different colum.
If colum "B" = Include Sum Column "C" of Includes only
If colum "B" = Exclude Sum Column "C" of Excludes only.

Example of what I am trying to say.

A               b                   c
File1        Include           10.00
File2        Include           20.00
File3       Exclude           10.00

Total 1 = 40.00
Total Include = 30.00
Total Exclude = 10.00

Question by:danorme
    LVL 34

    Accepted Solution

    You use a similar method to your existing code, but with a filter on the column containing the "Include/Exclude" value.  On these lines

                y = dgotherinc.DataSource.compute("SUM([Brw Amount])", "Colb = 'Include'")


                z = dgotherinc.DataSource.compute("SUM([Brw Amount])", "Colb = 'Exclude'")


    Author Comment

    Well, I am close, but not there yet.  Here is my code and here is the error?

            If DsDebts1.tbldebts.Rows.Count > 0 Then
                varCalcInclude = dgDebts.DataSource.compute("Sum([monthpay])", "[action]='Include'")
                varCalcExclude = dgDebts.DataSource.compute("Sum([monthpay])", "[action]='Exclude'")
                txtInclude.Text = varCalcInclude
                txtExclude.Text = varCalcExclude
                txtDebtTtl.Text = varCalcInclude + varCalcExclude
            End If

    An unhandled exception of type 'System.Data.DataException' occurred in microsoft.visualbasic.dll

    Additional information: Invalid usage of aggregate function Sum() and Type: String.
    LVL 34

    Expert Comment

    What that error message appears to be saying is that the [monthpay] column on which you are trying to use the Sum() function is of String datatype rather than a Numeric datatype.  Does that ring any bells with you?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now