Results 1 to 12 of 12

20030922, 20:09 #1
 Join Date
 Apr 2003
 Location
 Calgary, Alberta
 Posts
 327
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030922, 20:20 #2
 Join Date
 May 2002
 Location
 Toronto, Ontario, Canada
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>

20030922, 23:01 #3
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
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 FloatingPoint 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 FloatingPoint 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

20030923, 02:28 #4
 Join Date
 Apr 2003
 Location
 Calgary, Alberta
 Posts
 327
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20030923, 03:11 #5
 Join Date
 Sep 2001
 Location
 Wellington, Wellington, New Zealand
 Posts
 181
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Round Not Happening Correctly (Access 2000)
Hi
Would the following work??
Round(Amount+.000499,2)
Regards
WTH

20030923, 03:40 #6
 Join Date
 Apr 2003
 Location
 Calgary, Alberta
 Posts
 327
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Round Not Happening Correctly (Access 2000)
nope

20030923, 06:16 #7
 Join Date
 Jun 2002
 Location
 Mt Macedon, Victoria, Australia
 Posts
 3,993
 Thanks
 1
 Thanked 45 Times in 44 Posts
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>
Regards
John

20030923, 08:41 #8
 Join Date
 Nov 2001
 Location
 Arlington, Virginia, USA
 Posts
 1,394
 Thanks
 0
 Thanked 3 Times in 3 Posts
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 divisionbyzero error will result. For more details refer to MSKB 196652.
HTH

20030923, 12:04 #9
 Join Date
 Apr 2003
 Location
 Calgary, Alberta
 Posts
 327
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Round Not Happening Correctly (Access 2000)
OK, that works great. the only thing is how can I round properly in my querydef?

20030923, 12:20 #10
 Join Date
 May 2002
 Location
 Toronto, Ontario, Canada
 Posts
 314
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030924, 04:11 #11
 Join Date
 Apr 2003
 Location
 Calgary, Alberta
 Posts
 327
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030924, 04:38 #12
 Join Date
 Apr 2003
 Location
 Calgary, Alberta
 Posts
 327
 Thanks
 0
 Thanked 0 Times in 0 Posts
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