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

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].[

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

thanks in advance

Microsoft Access

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

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.

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 questionHamed 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
```

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

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_T

/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

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.

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

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"

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.

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

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

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

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

/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

>(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.

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

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.

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?