Anyone had different results between Access calculations and Excel

[DoogieB]

I've been programming in Access since 1995, but this is the first time I've
run across what appears to be some discrepancies between calculations of data
in Access and the same calculations being done in Excel with imported raw
data from Acess. I've looked over my formatting, formulas, and data sources,
and can find no answer except that there must be some problem int this case
with Access calculations of floating numbers. Has anyone else had an issue
where Access calculations of data differed from the same calculations in
Excel? Any suggestions?

 

[Albert D. Kallal]

You have to be careful, as the 1st thing you lean in a computing course is
that rounding occurs.


eg:
Public Sub TestAdd()

Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub

Here is the actual outpput of the above:

1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1

You can see that after just 7 addtions..already rounding is occuring !

and if we add the follwing line of code to the end of the above:

if MyNumber = 10.1 = True then

msgbox "the number is 10.1"
else
msgbox "the number is somthing else"
endif


The above will actuall produce:

the number is something else

In other words, even when it SHOWS 10.1, and we "test" for that value, it
fails.

If you doing additions of data, hopefully your numbers do not need more then
4 decimal places. If that is the case, then use a currency field, NOT
floating number type field. -- floating numbers in a computer are only a
approximate representation of the actual value (we can't really represent
fractional values).

A currency field is actually scaled integer (that means it is a integer with
some decimal settings not an actual computer floating point number).

If you can't use currency, then consider using "decimal field". It allows up
to 28 digits, and a scale to that...

Currency is better if your numbers never need more then 4 decimal point
because we have a native built in currency data type. if you use
decimal..then you need to use a variant type.

 

[DoogieB]

Thanks, Albert. I appreciate what you're saying, and while I didn't know
quite all the detail, I do have a lot of experience with Access programming
professionally and am aware of the rounding issues. My understading is that
in reports (and text fields) that the format property mostly affects how
something is displayed. Unfortunately, my calculated field does need to be
quite precise. In the Access documentation I'm seeing a lot of use of the
expression "follow the settings specified in the regional settings of Windows
for negative amounts" along with what the particular formatting choice should
display in the way of comma, decimal point, and currency sign. This isn't
telling me anything about what data type is "under the hood" doing the work
for the calculating formula.

I guess I really was interested to know if there are any indications of a
documented flaw in Access in this case, rather than user error involving
rounding. I'm afraid I may just have to reevaluate the underlying sources of
my calculations and try to have closer control over the calculating field
itself, perhaps with use of a custom function that could enforce the data
type I wish.

Thanks for your help considering this. I welcome any follow-up comments you
might have.

Best Regards,

DoogieB

 

[Chuck]

Thanks, Albert. I appreciate what you're saying, and while I didn't know
quite all the detail, I do have a lot of experience with Access programming
professionally and am aware of the rounding issues. My understading is that
in reports (and text fields) that the format property mostly affects how
something is displayed. Unfortunately, my calculated field does need to be
quite precise. In the Access documentation I'm seeing a lot of use of the
expression "follow the settings specified in the regional settings of Windows
for negative amounts" along with what the particular formatting choice should
display in the way of comma, decimal point, and currency sign. This isn't
telling me anything about what data type is "under the hood" doing the work
for the calculating formula.

I guess I really was interested to know if there are any indications of a
documented flaw in Access in this case, rather than user error involving
rounding. I'm afraid I may just have to reevaluate the underlying sources of
my calculations and try to have closer control over the calculating field
itself, perhaps with use of a custom function that could enforce the data
type I wish.

Thanks for your help considering this. I welcome any follow-up comments you
might have.

Best Regards,

DoogieB

Open Excel
Enter 1 in A1
Enter3 in A2
Enter =a1/A2 in A3
Enter = a3 * 10 - A$2 in A4
Copy A4 to A5 : A25
look at the results.

This is because in computer calculations, no mater how many decimal places you
format the cells A1 : A25, The last place is always *uncertain* . And this
*uncertainty* creeps forward with each successive calculation. A hand held
electronic *Calculator* does math a little differently and the same *trick*
will run the value to zero then -3, -33, -333 etc.

Chuck
--

 

[Guest]

1) Access rounds differently than Excel at every step of the calculation.

2) Access round differently than Excel before display.

Access internally uses Single or Double floating point numbers.

Excel internally uses 8087 IEEE floating point numbers.

Excel rounds to Double floating point only at the end of the calculation.
Then Excel rounds for display.

I don't think this is a flaw, but it is different. VB uses standard
floating point numbers. Excel has a dedicated calculation engine.
Excel is the standard: Compatibility is not an issue. But it is tightly
bound to the hardware platform.

Still, if you do it properly, Access/VBA with Double accuracy
is almost always close enough to Excel. It would be a very
unusual problem that didn't give the same answer as either
(your Excel spreadsheet) or (the other party reference) when
done carefully. Don't use Single.

(david)

 

[Jamie Collins]

Access internally uses Single or Double floating point numbers.

Access's SQL engine does not e.g. SELECT TYPENAME(0.5) returns
'Decimal'.

Jamie.

--

 

[Larry Linson]

Access's SQL engine does not e.g. SELECT
TYPENAME(0.5) returns 'Decimal'.

And, as your comment does not appear to deal with the subject under
discussion: internal calculations, your point is exactly _what_?

 

[Guest]

The example demonstrates that a manifest constant may be
treated like a Decimal by Jet, which, on the face of it, falsifies
my assertion that Access internally uses Single or Double
floating point numbers.

So questions are (1) What calculation engine is used by Jet?
(2) What is the internal representation for Decimal?
and (3) Is the treatment of manifest constants relevant?

I think that SQL will probably use a VBA like calculation engine:
that is, Jet is probably compiled by MSC++, the same as VBA.

Excel is not. There is no reason to expect that the hand-tuned
assembly language calculation engine has been discarded.

As everyone knows, Decimal is a kludge that has been tacked
on to VBA and Jet. It is extremely unlikely that VBA/Jet
inherited any of the Excel calculation engine: it is also unlikely
that any of the existing Single/Double calculation engine was
touched.

Unless anyone demonstrates otherwise, my guess is that the
treatment of manifest constants is an anomaly.

(david)