Truncate a numeric field

  • Thread starter Thread starter Fei
  • Start date Start date
F

Fei

Hi,

I got a problem. I don't know how to solve it. I have two columns, NetEach
and Rate. Then I create an expression Column, Total, like this

Table1.Columns.Add("Total", GetType(Decimal), "NetEach * Rate")
i.e. Total = NetEach * Rate

So if NetEach = 10, and Rate = 0.1299, then Total = 1.299. But I need 1.29,
not 1.299,not 1.300. That means I don't want Rounding. I just want two
digits after the decimal point. Is this possible?

I try below, but it gives me Rounding which I don't want.
Table1.Columns.Add("Total", GetType(Integer), "Convert(Rate * NetEach *
100,System.Int32)")

Simply saying, If I need two digits after decimal point, how can I do it in
an expression column ?

Thanks in advance

Fei
 
I believe both System.Format and DataBinder.Eval will round. Here is a first
try idea (off the cuff)

1. Total = NetEach * Rate * 100 = 12799.99999
2. Convert to Integer = 12799
3. Divide by 100 as Decimal = 127.99

You then have exactly 2 digits without any rounding. Experiment as
necessary.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Hi Fei,

There are probably many ways to do this. Here's one:
Dim xr As Decimal

Dim sxr As String

xr = 1.299

sxr = CStr(xr)

sxr = Mid(sxr, 1, InStr(sxr, ".") + 2)

xr = CDec(sxr)

MessageBox.Show(xr)

xr = 1.3

sxr = CStr(xr)

sxr = Mid(sxr, 1, InStr(sxr, ".") + 2)

xr = CDec(sxr)

MessageBox.Show(xr)

HTH,

Bernie Yaeger
 
How can I use Format or DataBinder.Eval functions in the expression of an
computed column?

i.e. how can I put in below line
Table1.Columns.Add("Total", GetType(Decimal), "NetEach * Rate")

Thanks !

Fei
 
Sorry for confusing many helpers. I am trying to add an expression column in
a datatable. In that expression column I can set expression, such as Total =
NetEach * Quantity. How can I truncate that Expression column?

Example
Table1.Columns.Add("Total", GetType(Decimal), "NetEach * Rate")

Fei
 
Fei said:
Sorry for confusing many helpers. I am trying to add an expression column in
a datatable. In that expression column I can set expression, such as Total =
NetEach * Quantity. How can I truncate that Expression column?

Example
Table1.Columns.Add("Total", GetType(Decimal), "NetEach * Rate")

Fei
Click to expand...
You would probably need to do that using sql syntax like for example use
something like Convert(numeric(18,2), 10 * 1.2979). Let me know if that
works
 
Dilip Krishnan said:
You would probably need to do that using sql syntax like for example use
something like Convert(numeric(18,2), 10 * 1.2979). Let me know if that
works
Click to expand...
No. that is not what i want. I don't want format Rate or NetEach column, I
want to format the result of Rate * NetEach.

Thanks though.

Fei
 
Back
Top