[RESOLVED] Compute with multiple filters

[billboy]

Is there a way to use the DataTable.Compute Method with 2 or more filters, i have googled a bit and cant seem to come up with answer?
If not perhpas there is another way to accomplish

Thanks for any help and or insight

Dim Dt As Decimal = NumericUpDown1.Value
                dtlist.Rows(i)("Value") = dtsold.Compute("Max(ClosePrice)", "Distance <=" & Dt & "")

I want something like this

Dim Dt As Decimal = NumericUpDown1.Value
                dtlist.Rows(i)("Value") = dtsold.Compute("Max(ClosePrice)", "Distance <=" & Dt & " and "BuildingSize<= MAXGla"")

in other words DT.Compute(Max(Col1), where Col2<=? and Col3 <=?

[jmcilhinney]

This really comes down to whether you know how to concatenate strings together or not. If you have multiple criteria to filter on then you simply AND or OR them together, just as the documentation says, e.g.

filter = "Column1 = someValue AND Column2 = someOtherValue"

If you want to use a variable for the values then, exactly as you always do, you concatenate the variable into the String:

filter = "Column1 = " & someValue & " AND Column2 = someOtherValue"

or use String.Format:

filter = String.Format("Column1 = {0} AND Column2 = someOtherValue", someValue)

[billboy]

This really comes down to whether you know how to concatenate strings together or not. If you have multiple criteria to filter on then you simply AND or OR them together, just as the documentation says, e.g.

filter = "Column1 = someValue AND Column2 = someOtherValue"

If you want to use a variable for the values then, exactly as you always do, you concatenate the variable into the String:

filter = "Column1 = " & someValue & " AND Column2 = someOtherValue"

or use String.Format:

filter = String.Format("Column1 = {0} AND Column2 = someOtherValue", someValue)

thanks that was my exact problem I had an extra "

btw I couldnt find how to use multiple filter in the documentation, just curious if you have a link to it, I had looked there first

Thanks again

[jmcilhinney]

I couldnt find how to use multiple filter in the documentation, just curious if you have a link to it, I had looked there first

Where exactly did you look? This is from the documentation for the DataTable.Compute method:

The second parameter, filter, determines which rows are used in the expression. For example, if the table contains a date column named "colDate", you could limit the rows with the following expression:

colDate > 1/1/99 AND colDate < 17/1/99

It also says this:

For rules on creating expressions for both parameters, see the DataColumn.Expression property.

If you follow that link you get to a detailed topic that includes this:

Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

Seriously, where did you look?

[billboy]

LOL

I looked right there, that is how I even thought it could be done and why I was trying to use the AND, and how I learned to create an expression

dtsold.Compute("Max(ClosePrice)", "Distance <= " & Dt & " And GLAMax <= BuildingSize")

"GLAMax" is a column expression. What through me off was the quotes and thinking I had to quote the "ColumnName", so had I just followed the Documentation closer I would have solved much sooner.

The quotes are for the whole filter as a string?

Thanks again