ACCESS 2010 (32-BIT) ON w7 64 BIT SUBTRACTION ISSUES

Hi All,
does anyone know why when subtracting two numbers entered and stored with one decimal point the outcome can have a whole bunch? e.g 11.2 - 8 = 3.19999980926514

I have to jump through hoops to get it back to 1 decimal e.g. CDbl(Format((Nz([tbl_TE].[End_Time],0)-Nz([tbl_TE].[Start_Time],0)),'#.0')) AS Hours

when it should be end time - start time = hours.

thanks in advance
LVL 7
COACHMAN99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
This is normal due to the memory management storing numbers.
You notice this as well when using hand calculators. If you divide 1 by 3 you get 0.333333
if this number is multiplied by 3 it gives 0.999999 not 1, if we do it by hand on a paper calculator!
If you need better accuracy, declare the variables as currency which uses more decimal points to represent the number.

In Access 2013 on Windows 8 64 bit it gives the correct answer.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
COACHMAN99Author Commented:
Thanks for the response. It isn't normal, and my concern involves subtraction not division.

If you store 11.2 (in a double field), and subtract 8 (also stored in a double), you should get 3.2, not 3.19999980926514. As you say, it works Ok on other platforms.

There must be Microsoft acknowledgement, and hopefully a fix?
0
hnasrCommented:
It is normal, with any operation including subtraction, as far as memory storage is concerned.

   
Dim n1 As Single, n2 As Single
    n1 = 1234567.9
    n2 = 1234567.8
    Debug.Print n1 - n2
    n1 = 12.9
    n2 = 12.8
    Debug.Print n1 - n2

Result:
 0.125 
 9.999943E-02 

The fix is to use proper type such as double or currency:
    Dim n1 As Currency, n2 As Currency
    n1 = 1234567.9
    n2 = 1234567.8
    Debug.Print n1 - n2
    n1 = 12.9
    n2 = 12.8
    Debug.Print n1 - n2

Result:
 0.1 
 0.1 

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
This is a known issue for floating numbers, not specific for VBA, and you just have to deal with it. One method is to use the proper data type - that's what they are for.

Further, your formula is probably wrong as it will return excessive values if either time is Null. Thus you could use this much simpler formula:

Hours: Val(Format([tbl_TE].[End_Time] - [tbl_TE].[Start_Time], "0.0"))

/gustav
0
COACHMAN99Author Commented:
Thanks for the input.
As I stated above, the storage is defined as double, and it is not normal (in the case of subtraction)

If you store 11.2 (in a double field), and subtract 8 (also stored in a double), you should get 3.2, not 3.19999980926514. This type of precision-extrapolation only occurs with division.

It isn't a VBA issue (SQL), and thanks for the suggested formula. Yours returns zero instead of negative values where one is missing.

This problem only occurs on machines with the 32-bit runtime (not where full Access is installed). I am beginning to suspect something in the runtime (Ace.Oledb) libraries.
0
Gustav BrockCIOCommented:
> .. it is not normal (in the case of subtraction)

Well, perhaps not normal, but at least expectable.

And it is not related to division where you always must take care of rounding, it is specific for subtraction.
You just have to be aware of this and take your precautions. If four or fewer decimals is find, use data type Currency.

Here is a neat classic:

? 11.1-11

/gustav
0
hnasrCommented:
Actually it is related to division, because .1 is 1/10, and .1 cannot exactly be represented in binary. .25 can be exactly represented in binary.

Search for "Convert a Floating Point Decimal to Binary"
0
COACHMAN99Author Commented:
I agree. and also re-iterate that my issue is due to invalid subtraction results.
If I store 11.2  and subtract 8, the result should be 3.2

If I change the fields to Decimal (Precision 5, Scale, 1, decimals 1) from Single then the output is correct without manipulation. Unfortunately this sounds like 'treating the symptoms rather that the cause.'
0
hnasrCommented:
In fact the output still not correct, but because of using more decimal places then and rounding to fewer decimals.

This is in immediate window
?11.1-11
 9.99999999999996E-02
?round(11.1-11,1)
 0.1
0
Gustav BrockCIOCommented:
> Actually it is related to division ..

Well, yes of course. What I tried to explain is that you don't see this issue when you perform division (other than what's expected as you show with 1/3 which cannot be expressed by a decimal number), neither addition nor multiplication, only when subtracting.
The latter, however, includes addition with one or more negative values as well as comparisons like:

a = b

because the compiler perform this by checking if:

a - b = 0

The well-known way to handle this without changing data type is to test if:

Abs(a - b) < 0.0001 (or some other small value fitting the context)

> If I change the fields to Decimal (Precision 5, Scale, 1, decimals 1) from Single then the output is correct

True, but for table fields in Access, data type Decimals is known to be buggy, so you may introduce new issues. The preferred data type for four or less decimals is Currency.

That said, you don't need to change the data type at all, just be careful and prepared.

One proven way which doesn't change any values and accepts many decimals - only "makes it behave as expected" - is to use CDec():

CDec(11.1) - 11 => 0.1

Thus, if you need no rounding, you could use:

Hours: CDec([tbl_TE].[End_Time]) - CDec([tbl_TE].[Start_Time])

or, as you don't have more than four decimals:

Hours: CCur([tbl_TE].[End_Time]) - CCur([tbl_TE].[Start_Time])

and to accept Null values:

Hours: CCur(Nz([tbl_TE].[End_Time], 0)) - CCur(Nz([tbl_TE].[Start_Time], 0))

/gustav
0
hnasrCommented:
Sorry! I don't mean to divert discussion.
>(other than what's expected as you show with 1/3 which cannot be expressed by a decimal number)
This happens with 1/10 which is .1. It is related to how the decimal is converted to binary.

.1x2 =(0) .2x2 =(0) .4x2 =(0) .8x2 = 1.6 :(1) .6x2 = 1.2 :(1) .2x2 =(0) .4 ..... it repeats, hence rounding occurs. The binary .00011001...

1/4 => .25 x2 =(0) .5x2 =(1) 1. Ends here, hence no rounding. The binary .01

?11.1-11
 9.99999999999996E-02
?11.25-11
 0.25
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No points please.

<<If I change the fields to Decimal (Precision 5, Scale, 1, decimals 1) from Single then the output is correct without manipulation. Unfortunately this sounds like 'treating the symptoms rather that the cause.' >>

  It is not.  As the orther have said, lack Decimal precision is a well known fact when single and double data types are used because it relies on floating point operations.

  If you want accurate decimal handling, then you *must* use some type of scaled integer (i.e. Decimal or Currency data types).

  That's simply the way computers work and it's no different because of OS or language.  The result may vary from one to the next, but all will be inaccurate.

Jim.
0
COACHMAN99Author Commented:
Thanks everyone for your input
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.