Re: Anyone had different results between Access calculations and E

On Mon, 16 Jul 2007 09:52:07 -0700, DoogieB <DoogieB@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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

"Albert D. Kallal" wrote:

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.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx



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


.

Relevant Pages

  • Re: Anyone had different results between Access calculations and Excel
    ... Dim MyNumber As Single ... You can see that after just 7 addtions..already rounding is occuring! ... If that is the case, then use a currency field, NOT ...
    (microsoft.public.access.reports)
  • Re: best practices.rounding
    ... And, if using Currency, whether for actual currency or non-currency situations, ... greater precision at the individual transaction level the cumulative rounding ... can differ from the sum of the individually rounded amounts. ...
    (microsoft.public.access.gettingstarted)
  • Re: Pennies problem
    ... Client complains that there is sometimes difference in pennies in tax ... Dim Tax as Currency ... Then we must consider rounding. ...
    (comp.databases.ms-access)
  • RE: best practices.rounding
    ... discrepancy with manual addition or subtraction when a Currency data type is ... greater precision at the individual transaction level the cumulative rounding ... can differ from the sum of the individually rounded amounts. ...
    (microsoft.public.access.gettingstarted)
  • Re: Sum not totalling correctly
    ... At what point do I need to perform the rounding? ... > The Currency data type stores the value to 4 decimal places, ... Create a query into this table. ... >>> where Amount is the name of the Currency field you are trying to Sum. ...
    (microsoft.public.access.forms)
Loading