# question

HowardArner-3009 asked ·

## Dataset Column Expression Math Error when using convert

OK, this has me stumped. I am just trying to round a column using an expression in datatable column. Here is a program that shows the behavior i am not expecting

``````     Dim T As New DataTable
Dim Ty As System.Type
Ty = System.Type.GetType("System.Double")
T.Columns.Add("A", Ty)
T.Columns.Add("B", Ty)
T.Columns.Add("C", Ty, "A*B")
T.Columns.Add("D", Ty, "Convert(A*B*100,System.Int64)/100")

T.Rows.Add(3, 3.331)
T.Rows.Add(3, 3.333)
Dim S As String = String.Format("A: {0} B: {1} C:{2} D:{3}", T.Rows(0).Item(0), T.Rows(0).Item(1), T.Rows(0).Item(2), T.Rows(0).Item(3))
Debug.Print(S)

S = String.Format("A: {0} B: {1} C:{2} D:{3}", T.Rows(1).Item(0), T.Rows(1).Item(1), T.Rows(1).Item(2), T.Rows(1).Item(3))
Debug.Print(S)
``````

The output of the above is:

A: 3 B: 3.331 C:9.993 D:9.99
A: 3 B: 3.333 C:9.999 D:10

Notice what is happening on the second case, the number is being truncated. I have tried many variations on the formula and cannot get it to handle it correctly. some kind of voodoo is happening. I tried converting the 100 to a decimal, converting it to a double. I cannot get it to function. What am i missing?

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

### 1 Answer

Viorel-1 answered ·

Show the expected result and try this expression: "Convert(A*B*100 - 0.49, System.Int64) / 100".

If negative numbers are possible, then try "Convert(A*B*100 + iif(A*B>=0, -0.49, +0.49), System.Int64) / 100".

· 3 ·

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much, that is something i just did not think of! I had tried convert, you solution is more elegant. I just wonder what is going on internally that causes it to not function as expected in the second case, but function as expected in the first.

I used your second example in the test code and result is as expected.

A: 3 B: 3.331 C:9.993 D:9.99
A: 3 B: 3.333 C:9.999 D:9.99

0 Votes 0 ·

It seems that Convert does not truncate (remove the part after ‘.’) but round the values: 999.3 becomes 999, and 999.9 becomes 1000.

Subtracting 0.49 before rounding results in truncation: 999.3-0.49=998.81, rounded to 999, and 999.9-0.49=999.41, rounded to 999.

1 Vote 1 ·

Don't you love how they document that behavior? The documentation on convert says nothing about rounding.... nor does it say anything about truncation... oh well... IMHO it should truncate but cest la vie.

0 Votes 0 ·