Solved

# DataGrids and Calculations

Posted on 2006-04-25
230 Views
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

Thanks.
0
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'")

and

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

Roger
0

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.
0

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?

Roger
0

## Featured Post

### Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net 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…