459,441 Members | 1,261 Online
Need help? Post your question and get tips & solutions from a community of 459,441 IT Pros & Developers. It's quick & easy.

rounding in access

 P: 12 I have a number 6.51495989352464 that I need to round to 6.51 I am using the following code: Public Function MyRound(dblInput As Currency, intDecimal As Integer) As Double If dblInput < 0 Then MyRound = -Int(CDec((Abs(dblInput) * 10 ^ intDecimal) + 0.5)) / 10 ^ intDecimal Else MyRound = Int(CDec((dblInput * 10 ^ intDecimal) + 0.5)) / 10 ^ intDecimal End If End Function When I step into the code what is actually going through the MyRound code is 6.515 so it makes sense that it is rounding to 6.52 but I do not know why it is rounding from 6.51495989352464 to 6.515. It's probably very simple but I'm not seeing it. Mar 8 '10 #1

I changed dblInput As Currency to dblInput As Double ... it seems to work now

5 Replies

 P: 12 one other wierd quirk: for some reason the code is multiplying ( 210.16 ) * ( 3.09999994933605E-02 ) it should be multiplying ( 210.16 ) * ( 0.0310) I know it's not much of a difference but the 0.0310 is being pulled directly from a table where the field is defined as Number-Single-Fixed (4 decimal places) Thanks! Mar 8 '10 #2

 100+ P: 122 Have you tried using the built in round function? Expand|Select|Wrap|Line Numbers Round(6.51495989352464, 2) =6.51 Mar 8 '10 #3

 P: 12 The built in round function in Access uses Banker' Rounding logic. Banker's rounding rounds .5 up sometimes and down sometimes. I need it to be Symmetric arithmetic rounding where it rounds .5 away from 0. Similar to Excel Worksheet Round function. Besides it seems to be rounding correctly but it looks like the number is being rounded before it even gets to my rounding code. I am going to try changing dblInput to Double in the code and see if that corrects it. Mar 8 '10 #4

 Expert 5K+ P: 8,701 The result that I am getting is 6.52, namely: Expand|Select|Wrap|Line Numbers Public Function MyRound(dblInput As Currency, intDecimal As Integer) As Double   If dblInput < 0 Then     MyRound = -Int(CDec(Abs(dblInput) * (10 ^ intDecimal)) + 0.5) / (10 ^ intDecimal)   Else     MyRound = Int(CDec(dblInput * (10 ^ intDecimal)) + 0.5) / (10 ^ intDecimal)   End If End Function Expand|Select|Wrap|Line Numbers Debug.Print MyRound(6.51495989352464 ,2) OUTPUT: Expand|Select|Wrap|Line Numbers 6.52 Mar 8 '10 #5

 P: 12 I changed dblInput As Currency to dblInput As Double ... it seems to work now Mar 8 '10 #6