Round when a Truncate is needed

[Quehay]

I've got a view that's feeding an Access form. Although I'm using Round(field,2) for output I still get the trailing zeros that correspond to the original field precision. What I need is something akin to Oracle's Truncate, that actually gets rid of unwanted precision.

Is there a way to do this without a cumbersome string convert/parse approach? (I've tried using the FORMAT within Access but it doesn't seem to recognize what's coming at it correctly.)

TIA

 

[MeanGreen]

Try this:

select convert(decimal(8,2),Round(field,2)) from yourtable

Hope this helps.

 

[lemosley01]

How about using cast/convert to convert to an integer.

 

[Quehay]

Thanks MeanGreen--that's a good approach.

 

[Quehay]

Unfortunately it generates an overflow error...

 

[MeanGreen]

How big is your number? Can you post the query you are running so I can see how you are casting/converting? Integer can handle over 2 billion, however smallint can only handle 32768.

Hope this helps.

 

[Giovaanni]

Well ROUND(field,0,1) is supposed to truncate but it doesn't, at least I can't make it work. I have had success in access using FIX(number) to truncate the value but I need to get it working inside of SQL Server so I can do some calculations on the server side instead of on the Access client.

 

[MeanGreen]

Again, can you post your query so I can see what you are doing? Integer can handle up to 2 billion, so you should not get an overflow. Do you know what value is causing the overflow?

Thank you.