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

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?

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 

Select allOpen in new window

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

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.

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

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"

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

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

> 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

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

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.

Thanks everyone for your input