rounding

[vb4me]

Can anyone help me with an issue I have had for a while.

When I have a figure calculated and it comes out as say 4.78. I then display this as 2 decimals which appears as 4.8.

If I have multiple values so 4.78 + 10.833=15.613 - this will appear as 15.61 although the values to 2 decimal places are 4.8 + 10.83= 15.63

As you can see the end result is different. When you are talking about money this can cuase problems with balances.

What do I need to do to have the value set to the 2 decimal value and not hold the real value underneath.

 

[SgtJarrow]

I guess my question is what version of Access are you using. I am using 97 myself, and to my knowledge, what you want to do can't be done. I believe with Access 2000 came a new number type, decimal. From what I know, this number type allows you to set the exact number of decimal places to store. In 97, Access stores its default number (which is something like as many as it can display), but only displays whatever you tell it....i.e. your problem with visible decimals and actual decimals used in calculations. I work for a banking firm and therefore have looked into this myself..... "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[jobo123]

Hi,

You can round numbers in Access97 with Banker's Interval Rounding. I use it for currencies. The following example is the code for a query that gives a Bankers Interval Rounding for a number called My_Number and presents the results as a new field called My_Rounded_Number.

My_Rounded_Number: CCur(CLng([My_Number]))

Hope this helps, jobo123, UK.

 

[Henaniy]

Try Format(CCur(Value),"$ 0.00&quot

Or CCur(Format(Value, "$ 0.00&quot

 

[Jeremy1952]

In Access 2000, you can use the "round" function exactly as you do in Excel: =round([Field],2) in a text box or query returns the value of [Field] rounded to two decimal places.

 

[jenkirk]

I was having the same issue and was able to work around by using =Sum((CInt([fieldname]*100))/100). We're currently using Access 97, but sounds like it won't be an issue once we upgrade.

 

[cheerio]

Take a look at using the Currency data type - this was designed to minimise rounding issues.

Ken

 

[evalesthy]

There are numerous Microsoft Knowledge Base articles that discuss the rounding problems even with Currency type fields. I experienced significant problems in one application and ultimately settled on the following function as the best solution:

Public Function BankersRounding(ByVal Number As Variant, NumDigits As Long, Optional UseBankersRounding As Boolean = False) As Double
Dim dblPower As Double
Dim varTemp As Variant
Dim intSgn As Integer

If Not IsNumeric(Number) Then
' Raise an error indicating that
' you've supplied an invalid parameter.
Err.Raise 5
End If

dblPower = 10 ^ NumDigits
' Is this a negative number, or not?
' intSgn will contain -1, 0, or 1.
intSgn = Sgn(Number)
Number = Abs(Number)

'Do the major calculation.
varTemp = CDec(Number) * dblPower + 0.5

'Now round to nearest even, if necessary.
If UseBankersRounding Then
If Int(varTemp) = varTemp Then
' You could also use:
' varTemp = varTemp + (varTemp Mod 2 = 1)
' instead of the next If ...Then statement,
' but I hate counting on TRue == -1 in code.
If varTemp Mod 2 = 1 Then
varTemp = varTemp - 1
End If
End If
End If
'Finish the calculation.
BankersRounding = intSgn * Int(varTemp) / dblPower

End Function

I found it on the web but cannot remember where - so my apologies to whoever wrote it.