# Thread: Round Not Happening Correctly (Access 2000)

1. ## Round Not Happening Correctly (Access 2000)

I have a problem whre the rounding is not happening properly. In a query, I have Round("BC * KitAmount, 2) which would give 10.025. For some reason, it will round to 10.02 instead of 10.03. Any ideas how to fix this?

Thanks

2. ## Re: Round Not Happening Correctly (Access 2000)

Okay... I'm completely confused... Since when does Access have a Round function?
I know I'm still using Access 97 at work, but I have 2000 on my home machines and I've never seen that one...
I'm going to have to peek when I get home... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Wish I could help... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

3. ## Re: Round Not Happening Correctly (Access 2000)

This behavior is "by design". VB will round .5 to nearest even number. Example:

? Round(10.015,2)
10.02
? Round(10.025,2)
10.02
? Round(10.035,2)
10.04
? Round(10.045,2)
10.04

This is explained in MSKB article 28855:

CINT/CLNG Integer Assignment Round x.5 to Nearest Even Integer

Excerpt:

"When a numeric expression ending in .5 is assigned to an integer variable, a compiled Basic program will round the expression to the nearest even integer. For example, .5 converts to 0, 1.5 converts to 2, 2.5 converts to 2, and 3.5 converts to 4. This rounding to the nearest even integer occurs for the CINT and CLNG functions and for an integer division assigned to an integer or long integer variable. This behavior is a feature of the IEEE Floating Point Standard.

"This type of integer rounding complies with the following IEEE standard:

'If the difference between the unrounded operand and the rounded result is exactly one half, the rounded result is even' (Section 5.5 of the "IEEE Standard for Binary Floating-Point Arithmetic")

"The purpose of this behavior is to prevent an average upward (or downward) bias as various calculations are rounded. If the number is always rounded up, there would be an upward bias in calculations. Rounding to the nearest even number averages out; therefore, no rounding bias occurs."

See article for further details. Can't argue with the "IEEE Standard for Binary Floating-Point Arithmetic"....

Re: Excel Round function: see MSKB article 194983:

PRB: Round Function different in VBA 6 and Excel Spreadsheet

"The Round() function in an Excel spreadsheet uses Arithmetic rounding, which always rounds .5 up (away from 0). The Round() function in Visual Basic for Applications 6, uses Banker's rounding, which rounds .5 either up or down, whichever will result in an even number."

See article for more info. For a VERY detailed explanation of various rounding methodologies, and how to implement your own custom rounding functions (with code samples), see MSKB Art 196652:

HOWTO: Implement Custom Rounding Procedures

HTH

4. ## Re: Round Not Happening Correctly (Access 2000)

Oh my, I honestly think that is one of the dumbest things I have seen. But anyway, back to my problem. How can I do a simple "Arithmetic" rounding is a query?

5. ## Re: Round Not Happening Correctly (Access 2000)

Hi

Would the following work??

Round(Amount+.000499,2)

Regards
WTH

nope

7. ## Re: Round Not Happening Correctly (Access 2000)

A way around this is to create your own custom rounding function that does what you want.

<pre>Function fnmyRound(mynumber As Single, places As Integer) As Single
' a function that rounds off a single to specified dec places, and rounds 5 down
Dim mynewnumber As Single

mynewnumber = mynumber * (10 ^ places)
If mynewnumber Mod 1 <= 0.5 Then
mynewnumber = mynewnumber 1
Else
mynewnumber = mynewnumber 1 + 1
End If

mynewnumber = mynewnumber / (10 ^ places)
fnmyRound = mynewnumber
End Function
</pre>

8. ## Re: Round Not Happening Correctly (Access 2000)

Looking at the custom Rounding functions provided in MSKB article 196652, I think this one is closest to what you want (modified from example in article):

Function RoundSymUp(ByVal Num As Double, _
Optional ByVal Factor As Double = 1) As Double

Dim dblTemp As Double

dblTemp = Fix(Num * Factor)
RoundSymUp = (dblTemp + IIf(Num = dblTemp, 0, Sgn(Num))) / Factor

End Function

This is the "SymUp" rounding function, which "symmetrically rounds fractions up." Example of use:

? RoundSymUp(1.05,10)
1.1
? RoundSymUp(1.15,10)
1.2
? RoundSymUp(1.25,10)
1.3
? RoundSymUp(1.35,10)
1.4

? RoundSymUp(1.015,100)
1.02
? RoundSymUp(1.025,100)
1.03
? RoundSymUp(1.035,100)
1.04

? RoundSymUp(-1.05,10)
-1.1
? RoundSymUp(-1.15,10)
-1.2
? RoundSymUp(-1.25,10)
-1.3

Note optional Factor argument which specifies precision of rounding. Article explains: "If the factor is omitted, then the functions return an integer created by one of the above methods. If the factor is specified, the number is scaled by the factor to create different rounding effects. For example AsymArith(2.55, 10) produces 2.6, that is, it rounds to 1/factor = 1/10 = 0.1." If you specify a Factor of 0, a division-by-zero error will result. For more details refer to MSKB 196652.

HTH

9. ## Re: Round Not Happening Correctly (Access 2000)

OK, that works great. the only thing is how can I round properly in my querydef?

10. ## Re: Round Not Happening Correctly (Access 2000)

Just call the function in your field definition...
Say you want a field called "fldAmount" rounded...
In your query def just use ... fldRndAmount: RoundSymUp(fldAmount)
Or if you use the factor... fldRndAmount: RoundSymUp(fldAmount, 10)

HTH

11. ## Re: Round Not Happening Correctly (Access 2000)

Hi

Looking at your solution still will round 12.255 to 12.25, even if I change the line
If mynewnumber Mod 1 < 0.5 Then

Is there something that I am missing. Basically, if it is .05 or.15 and above, it should round up.

Thanks

12. ## Re: Round Not Happening Correctly (Access 2000)

OK, here is a way that works how the rounding is supposed to happen (for me anyways). It is a slighly modifed version for Microsoft

Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double

Dim dblTemp As Double
Dim lngTemp As Long

dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)

If lngTemp = dblTemp Then
RoundToNearest = dblNumber
Else
If (dblTemp - lngTemp) < 0.5 Then
'If IsMissing(varUp) Then
' round down
dblTemp = lngTemp
Else
' round up
dblTemp = lngTemp + 1
End If
RoundToNearest = dblTemp * varRoundAmount
End If
End Function

Now I have to make sure it is applied everywhere [img]/forums/images/smilies/sad.gif[/img]

Thanks for the help everyone

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•