ARGH! Access can't add up?!?

  • Thread starter Thread starter Pecanfan
  • Start date Start date
P

Pecanfan

I've had the following formula in various forms and reports for calculating
VAT for years and I've just noticed a problem with it:-

=Int(([InvoiceTotalNET]*(([cbxVATRate].[Column](0))/100))*100)/100

Now, you'd expect when InvoiceTotalNET = 108 and cbxVATRate = 17.5 that the
above would come out as 126.90 but NO - for some reason it's coming out at
126.89!!! Same happens for 10800 (this comes out at 12689.99) BUT 1080
comes out at the correct value, 1269.00. What the... ?!?!

Can anyone explain this? PLEASE??? :-)

Andy
 
Pecanfan said:
I've had the following formula in various forms and reports for
calculating
VAT for years and I've just noticed a problem with it:-

=Int(([InvoiceTotalNET]*(([cbxVATRate].[Column](0))/100))*100)/100

Now, you'd expect when InvoiceTotalNET = 108 and cbxVATRate = 17.5 that
the
above would come out as 126.90 but NO - for some reason it's coming out at
126.89!!! Same happens for 10800 (this comes out at 12689.99) BUT 1080
comes out at the correct value, 1269.00. What the... ?!?!

Can anyone explain this? PLEASE??? :-)
Click to expand...

Are you sure your formula's right? I get 18.9 when I do the above. To get
your figure, I have to do this:

=[InvoiceTotalNET] + [InvoiceTotalNET] * [cbxVATRate].[Column](0)] / 100

The error you're seeing is called floating point error. Most versions of
BASIC (including VBA) use floating point numbers to represent data and
there's some rounding error.

Tom Lake
 
Likely to be the problem of truncation (Int truncates) combined with
rounding errors in converting from dec to binary for internal
representation.

Try using CInt instead.
 
=Int(([InvoiceTotalNET]*(([cbxVATRate].[Column](0))/100))*100)/100
Are you sure your formula's right? I get 18.9 when I do the above. To get
your figure, I have to do this:
Click to expand...

Are you sure? That's copied and pasted straight out the form.
=[InvoiceTotalNET] + [InvoiceTotalNET] * [cbxVATRate].[Column](0)] / 100

The error you're seeing is called floating point error. Most versions of
BASIC (including VBA) use floating point numbers to represent data and
there's some rounding error.
Click to expand...

Bugger. Cheers! :-)

Andy
 
Just coming back to this one briefly - is it possible for these floating
point issues to be intermittent? The same report started working shortly
after I posted this (I put it down to me fiddling with tables in an
uncontrolled manner) but now it's back to not being able to add up. Yet to
try the CInt thing... but just wondering why this would be an intermittent
issue.

Cheers again,

Andy



Van T. Dinh said:
Likely to be the problem of truncation (Int truncates) combined with
rounding errors in converting from dec to binary for internal
representation.

Try using CInt instead.

--
HTH
Van T. Dinh
MVP (Access)




Pecanfan said:
I've had the following formula in various forms and reports for calculating
VAT for years and I've just noticed a problem with it:-

=Int(([InvoiceTotalNET]*(([cbxVATRate].[Column](0))/100))*100)/100

Now, you'd expect when InvoiceTotalNET = 108 and cbxVATRate = 17.5 that the
above would come out as 126.90 but NO - for some reason it's coming out at
126.89!!! Same happens for 10800 (this comes out at 12689.99) BUT 1080
comes out at the correct value, 1269.00. What the... ?!?!

Can anyone explain this? PLEASE??? :-)

Andy
Click to expand...
Click to expand...
 
Pecanfan said:
Just coming back to this one briefly - is it possible for these
floating point issues to be intermittent? The same report started
working shortly after I posted this (I put it down to me fiddling
with tables in an uncontrolled manner) but now it's back to not being
able to add up. Yet to try the CInt thing... but just wondering why
this would be an intermittent issue.
Click to expand...

Floating-point inaccuracies depend on the specific values to be
represented. So if your data has changed, the calculation results could
come out the way you expect -- this time.
 
Just coming back to this one briefly - is it possible for these
Floating-point inaccuracies depend on the specific values to be
represented. So if your data has changed, the calculation results could
come out the way you expect -- this time.
Click to expand...

Sorry - should have explained in more detail. It's the same calculation.
Essentially 10800 x (17.5/100). Obviously the correct value is 1890 but
Access intermittently came out with 1889.99. I've fixed it properly now, by
changing the field where 17.5 was stored from General Number to Decimal,
still doesn't explain why the problem was intermittent though. Strange...

Andy
 
Pecanfan said:
Sorry - should have explained in more detail. It's the same
calculation. Essentially 10800 x (17.5/100). Obviously the correct
value is 1890 but Access intermittently came out with 1889.99. I've
fixed it properly now, by changing the field where 17.5 was stored
from General Number to Decimal, still doesn't explain why the problem
was intermittent though. Strange...
Click to expand...

If you're saying the exact same data gave different results at different
times, with no change in type, code, query, sequence, or anything, then
I'm flummoxed.
 
Back
Top