Rounding

[wllsth]

I have a problem with rounding.

I wish the field TxtCost to contain the rounded up result (2 Decimal Places) of Billable Hours * TxtRate. It appears correctly on the screen but it stores the unrounded figure in the table.

Here's my VBA code.

Private Sub Billable_Hours_AfterUpdate()
Me.TxtCost = Me![Billable Hours] * Me![TxtRate]
End Sub

 

[pbaldy]

Wrap your calculation in the Round() function (more info in Help).

 

[wllsth]

Wrap your calculation in the Round() function (more info in Help).

Thanks I thought it must be easy

 

[wllsth]

Not quite there it seems to round down NOT up, here's my code

Me.TxtCost = Round(Me![Billable Hours] * Me![TxtRate], 2)

Billable Hours(1.5) * TxtRate(10.39) gives 15.58 instead of 15.59

 

[pbaldy]

Access uses what's called "bankers" rounding. I recall an old thread here that talked about it and included a replacement function (post 8):

http://www.mdbmakers.com/forums/showthread.php?t=7139

 

[Kiwiman]

Thanks Paul, i was trying to figure away round this myself. Now I can look at this link. Learn something new every day.

 

[wllsth]

I found the following bit of code on the following website
http://support.microsoft.com/kb/196652/EN-US/

Function AsymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
AsymArith = Int(X * Factor + 0.5) / Factor
End Function

When I use it as follows :-
Private Sub Billable_Hours_AfterUpdate()
Me.TxtCost = Me![Billable Hours] * Me![TxtRate]
Me.TxtCost = AsymArith(Me.TxtCost, 100)
End Sub

I get the following result :-
Billable Hours(1.5) * TxtRate(4.09) = 6.135
Rounded to 6.13 NOT 6.14

I thought AsymArith would always round .5 upwards

 

[wllsth]

I've sorted the problem thanks with help from the following :-
Google "better round function vb" and you will find lots of code. Here is one:
http://www.developerfusion.com/code/4252/a-better-rounding-function/