Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Snigdha030
Starting Member
13 Posts |
 Posted - 2007-06-18 : 05:28:49
|
| select round(3.925,2) While using the the above SQL in MS SQL 2000 I am getting the result = 3.93But the same query in MS ACCESS 2000 gives result = 3.92Please suggest any rounding function which will give me the result (ex: 3.93 ) for the above query in MS ACCESS sql query. |
|
|
nheidorn
Starting Member
28 Posts |
Posted - 2007-06-18 : 17:34:18
|
You've run into one of the most frustrating bugs in Access. It's even worse when you get Excel into the mix.As long as your numbers have fewer than 14 positions left of the decimal point, this function will work:Function RoundTwo(X) RoundTwo = Int(X * 100 + 0.5) / 100End Function (Adapted from [url]http://support.microsoft.com/kb/97524/en-us[/url]) |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
nheidorn
Starting Member
28 Posts |
Posted - 2007-06-18 : 18:07:07
|
Microsoft decided to make floating point rounding in Access consistent with earlier versions of itself instead of consistent with other Microsoft products. The really frustrating part is that even if you explicitly convert the value to another data type, it still doesn't work as expected.Round(CCur(3.925), 2) gives the same results as:Round(3.925, 2) |
|
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 2007-06-18 : 21:53:26
|
| So where is the bug? |
|
|
|
Snigdha030
Starting Member
13 Posts |
Posted - 2007-06-19 : 05:01:53
|
| Thanks for all your responses.As I am loading data from MS ACCESS to MS SQL so came accross with the issue while rounding the amount fields. Will go for the solution Int(X * 100 + 0.5) / 100 Regards.Snig. |
|
|
|
|
|
|
Rounding in MS ACCESS SQL