Sums that sometimes don't add up.

[Awes]

Hi everyone

I am having a bit of fun (headaches) with an addition problem on a form.

At the bottom of the form there is a group of textboxes used for calculating values as follows:

Group A - totals the values in one of the two subforms
Group B - totals the values in the other subform
Sub Tot - Adds Group A and Group B together
VAT1 - Works out the VAT at 17.5% for Sub Tot
Group C - Retrieves a value from a query
VAT2 - Works out the VAT at 5% for Group C
Total - Adds Sub Tot, VAT1, Group C and VAT2 together

All values are in the format £0.00

This all seemed to be working fine until the User reported that some values of Total seemed to be £0.01 too high.

On futher investigation it appears that whenever one of the VAT values is equal to an exact amount of 0.5 pence it gives an error in the Total box by being £0.01 too high
i.e

Group A £20.00
Group B £11.00
Sub Tot £31.00
VAT1 £ 5.42 (actually £5.425)
Group C £ 5.00
VAT2 £ 0.25
Total £61.68 (actually £41.67)

All other instances work ok it is just when one of the VAT values is exactly a half pence. VAT1 or VAT2 display what looks like a rounded down value but the Totals box is rounding up.

Any ideas on how to solve this problem. I'm sure it is something to do with formatting but any pointers would be greatly appreciated.

Cheers

Awes

 

[Len Boorman]

Suggest that before doing anything you need to read up on the rounding. Not that you do not understand rounding. No not at all but if my memory serves me correctly the Access method of rounding can have some quirks.

I believe that
3.425 would round to 3.42 but
3.435 would round to 3.44 hmmmm curious

When Access meets an exact mid point it looks back at the previous digit to see if it is odd or even. Decision then to round up or down.

Please check cos the memory is getting on a bit.

Possible solution depending on what you actually want to happen is to add or deduct a very small amount that would have no effect on the actual value but say push it in the direction you want to go if faced with an exact mid point. Say add 0.00001 to the calculation then with a bit of luck you would never get the mid point situation. Bit of a risk involved depending upon your calculation. You need to figure out how many decimal points could be generated as a result of your calculation and then the add on (or take away) is 1 in the next decimal place.

HTH but check it out cos the memory chips are a bit old.

Unless of course anybody else out there knows better (probably)

Len B