Avatar of COACHMAN99
COACHMAN99 asked on

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
Microsoft Access

Avatar of undefined
Last Comment
COACHMAN99

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Hamed Nasr

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
COACHMAN99

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?
Hamed Nasr

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

Gustav Brock

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
COACHMAN99

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.
Gustav Brock

> .. 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
Hamed Nasr

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"
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
COACHMAN99

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.'
Hamed Nasr

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
Gustav Brock

> 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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Hamed Nasr

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
Jim Dettman (EE MVE)

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.
ASKER
COACHMAN99

Thanks everyone for your input
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.