Rounding

[Mez]

Hello all, hope one of you can help me before I throw myself under a bus!

Got an access query with a Net figure in one of the fields (£351.00). Working out the VAT with the rounding command (Round(([Net]*1.175),2). Getting £61.42. Should be £61.43 as the actual unrounded fig is £61.425. Any ideas???

 

[fuzzygeek]

I Confirmed your error

I have Access 2002 and get the same result set that you got. You may have to design your own rounding function. See the following article on floating point errors in ACCESS.

http://support.microsoft.com/default.aspx?scid=kb;en-us;210423&Product=acc

 

[KKilfoil]

The rounding rule I was taught in school was that when the last digit is a '5', you should round to the even (i.e. 0,2,4,6,8) value, sometimes rounding up, and sometimes down.
This was so that statistically, the average effect of the roundoff would be zero, given a large sample.

Perhaps Accesss follows this rule as well. So 22.045 rounds to 22.04, whereas 22.055 rounds to 22.06!

If you REALLY don't like this behaviour and always want to round 'up', try something this:

newvar: Round(TheValue+.0001,2)

 

[KKilfoil]

BTW, you guys have a 17.5% VAT !!?

 

[Rich]

BTW, you guys have a 17.5% VAT !!?

Yes, Gnats brew

 

[raskew]

Refer to your original statement.

If net = 351, multiplying by something greater than 1 (e.g. 1.175) will never = 61.XXX.

Try this from the debug window:

net = 351
withtax = net * 1.175
? withtax
412.425

tax = net * 0.175
? tax
61.425

? roundx(tax, 2)
61.43

gross = roundx(net * 1.175, 2)
? gross
412.43

with roundx being:

Function roundx(ByVal pNum As Double, pPlace As Integer) As Double
Dim varNum As Double
Dim varPrefix As Integer 'equates to -1, 0 or 1
Dim varHold As Double

varNum = pNum
varPrefix = pNum / Abs(pNum)
varHold = 10 ^ pPlace
varNum = (Abs(pNum) * varHold) + 0.5
roundx = varPrefix * Int(varNum) / varHold

End Function

HTH- Bob

 

[Mez]

Many thanx people, all my problems are now sorted. OK, rounding 1/2 pennies up aint all my problems, but hey, its a start.

 

[namliam]

If only i had half a penny for every time a half a penny "disappeared" *Sigh*

Regards

The Mailman