massive bug in MSAccess

[rlayberry]

Hi
I have found what looks like a huge bug in MSAccess.
When I have the following in a table in Access
1996 0.08715292
1996 12.98775
1996 1.987653
1996 0.09476294
1996 0.1012828
1996 1.25012E-06
1996 0.08638828
1996 0.09060681
1996 0.09808088
2010 128.9784
2020 1.568336E-05
and I save the table as a CSV file...I get the following
1996,0.09
1996,0.09
1996,0.08
1996,1.25012e-06
1996,0.10
1996,0.09
1996,1.98
1996,12.98
1996,0.08
2010,128.97
2020,1.568336e-05
the order is different, that is not a bug, just microsoft's stupid
implementation.
The problem is that some numbers in the second column have been
truncated. A closer look shows that numbers of the form a.b have been
truncated to 2 decimal places (as is set in the regional settings),
but numbers of the form a.bexp-c haven't. I don't know if there is
logic behind this or not. But...
a) I wouldn't expect Access to truncate ANY numbers when writing to a
csv file REGARDLESS of the regional settings for number
representation. To do so is dangerous and makes Access behave
differently on different machines.
b) IF it decides to truncate numbers on writing, I would expect it to
round the numbers off, rather than just truncate. Otherwise the
answer is not just less accurate, but ABSOLUTELY WRONG.
c) IF it decides to truncate numbers to 2 decimal places, I would
expect it to truncate ALL numbers, so as to minimise confusion.
Does anyone have any comments? I don't know whether this is really a
bug or there is some Microsoft logic behind it. If it is a bug, it
makes me very untrusting of MS Access generally.
Russ

[Steve Jorgensen]

On 8 Dec 2004 07:02:15 -0800, rl*******@hotma il.com wrote:

Hi
I have found what looks like a huge bug in MSAccess.
When I have the following in a table in Access
1996 0.08715292
1996 12.98775
1996 1.987653
1996 0.09476294
1996 0.1012828
1996 1.25012E-06
1996 0.08638828
1996 0.09060681
1996 0.09808088
2010 128.9784
2020 1.568336E-05
and I save the table as a CSV file...I get the following
1996,0.09
1996,0.09
1996,0.08
1996,1.25012 e-06
1996,0.10
1996,0.09
1996,1.98
1996,12.98
1996,0.08
2010,128.97
2020,1.56833 6e-05
the order is different, that is not a bug, just microsoft's stupid
implementation .
Not really stupid. A relational database does not specify a physical order
for data. If you want data exported in a particular order, use a query and
export that.
The problem is that some numbers in the second column have been
truncated. A closer look shows that numbers of the form a.b have been
truncated to 2 decimal places (as is set in the regional settings),
but numbers of the form a.bexp-c haven't. I don't know if there is
logic behind this or not. But...
a) I wouldn't expect Access to truncate ANY numbers when writing to a
csv file REGARDLESS of the regional settings for number
representation . To do so is dangerous and makes Access behave
differently on different machines.
Since a floating point number is stored in a binary floating format that
cannot be represented exactly as a decimal number, there is no way to export
it as decimal numeric text without approximation of some kind.
b) IF it decides to truncate numbers on writing, I would expect it to
round the numbers off, rather than just truncate. Otherwise the
answer is not just less accurate, but ABSOLUTELY WRONG.
Yes, rounding would be better, but it is in the nature of floating point types
to be "wrong", we should not have expectations of how they will be directly
exported.
c) IF it decides to truncate numbers to 2 decimal places, I would
expect it to truncate ALL numbers, so as to minimise confusion.
With floating point numbers, you don't know in advance what order of magnitute
is important. If all your numbers are in the .000001 scale, and all have 10
significant digits, and you arbitrarily round everything to .0001, then you
have discarded -all- the significant digits of all numbers, making them all
zero. That's why your calculator understands scientific notation.
Does anyone have any comments? I don't know whether this is really a
bug or there is some Microsoft logic behind it. If it is a bug, it
makes me very untrusting of MS Access generally.

If you expect exported numeric data to be accurate, store it in an accurate
numeric format such as Long or Currency. Otherwise, if you have a particular
expectation of how you want your numbers exported, create a query that uses
Format to convert the numbers to text in your preferred way, and export that.
If you do, the export will do what you specified.

[Albert D. Kallal]

> the order is different, that is not a bug, just microsoft's stupid

implementation.
Actually, in the old days of punched cards, and even some older file based
data systems, data was considered to be ordered.

So, data from punched cards, and even those old reel to reel tape systems
you see in old movies did in fact preserve order.

However, today, if you use Oracle, My-Sql, Sybase, and virtually all modern
database systems, the order of the data on the disk system is NOT known. In
fact, your application might be grabbing data from a system half way across
the world...and there might be 10 users adding, and deleting records. (and,
even more interesting in that ms-access is a relational database, the data
you are viewing as a single record actually might have come from TWO
different computers where records from both are combined into one view....a
relational database can do this!!). Because modern database systems are very
fluid, and allow multiple users to add, and delete data (as opposed to the
old punched cards of yester year), then the order of data is NOT possible to
maintain..or even know how it sites in the database system. About the only
thing you can be sure of is to ask the data engine to retrieve the data that
you need. The algorithm, and how the computer retrieves a individual records
is the subject of books, and MANY MANY different schemes exist on how to
retrieve data from a modern database engine. However, one thing for sure we
do know is that these schemes don't preserve the order of the data that it
was entered in. (and, if you got 10 people adding data to the file..which
order should the data be put in?

Should the order of the data be maintained by each individual (so they can
go back and check their work done during the day?).

or,

Should we order the data by each new record added? (and, if you got 15
people adding records...is this order of any value anymore? Likely not).

So, which should we use? By each person own order, or intermixed by each
person adding a record?

Really, the correct answer is to simply order the data in the order that you
need. The data engine builders long ago gave up on preserving order of data
entered..since it is of little value anymore. So, you can't make the assume
of data order in a modern system (this is database 101 here!). If you need
the data returned by each user, then you would order the database by

UsernameWhoAdde dTheReocrd
And then order by
TimeStamp (or date and time of entry).

And, if you don't care about the 10 people who entered data..but only care
about the actual order that records were added, then you have to order the
data by date, and time of entry. (and, this assumes you designed this into
your application). In your case, likely using a autonumber primary key field
would suffice (it increments by one for each record added..and I don't think
you need a timestamp here).

Further, what happens when one user starts entering some data...but another
user starts entering some data..but is FASTER...and finishes up sooner. You
can see that maintains order of data by physical disk or file order is a
concept that is just old, and out dated.

So, you are new to database concepts, and that is no problem! (you got a
whole new world to learn!!). Perhaps you are from a old time system that
used punched cards, or a old computer system that relied on data order. Text
files for example preserve order of the data placed in them..but data in
database has no such preserve.

The problem is that some numbers in the second column have been
truncated. A closer look shows that numbers of the form a.b have been
truncated to 2 decimal places (as is set in the regional settings),
but numbers of the form a.bexp-c haven't. I don't know if there is
logic behind this or not. But...

Again, this is issue of computers..and how numbers are resented. "real"
values in a computer are actually a appromaxaton. If you use integer values
(or so called whole values), then a computer can do a much better job.
integer, long, and the currency data types in ms-access are actually stored
as whole numbers..and thus not subject to rounding...or approximate errors.
If you are doing any financial business software...you better learn this
lesson in a hurry.

However, you are obviously new to computers..and again are not aware of how
they work. The following little piece of code
from ms-access will produce the following put out...and will be a surprse.
Read the code first..and imainge what the output will
look like...

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

So, if you are going to use a database, or in fact use computers for any for
work, it likely is a good idea that you do some reading up on how they work.
If you don't, then you will be like a first year doctor trying to treat
patients..but not yet know what you are doing. Without some education, and
some skills on your side..you will find many pitfalls and many surprises as
to how computers work.

I suppose the same thing can be said when working on a car, or that first
year medicinal student studying and discovering that germs existing in the
air, but you can't seem them.

Working on cars, or working on People, or working on computers takes a lot
of skill, and a lot of learning. You want to learn some stuff here else you
will be deemed incompetent.... or worse loose you job, or have lawsuits etc.
against you. And, if any consequences of using computers is only your
personal time..then you owe it to your self to learn how this stuff works.
Like working on a cars, learning medicine, or learning computers..the
results are immense in terms of personal satisfaction.

You can't become a doctor in a day..and the same applies to working with
computers...it is going to take some time on your part to learn all this
stuff...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.attcanada.net/~kallal.msn

[rlayberry]

>Not really stupid. A relational database does not specify a physical
order

for data. If you want data exported in a particular order, use a query andexport that.
A relational database may not specify an order, but the Microsoft
implementation does, in 2 ways. Firstly the order it defaults to
displaying it and secondly the order it writes it out. I would have
expected these to be the same for debugging purposes.

The problem is that some numbers in the second column have been
truncated. A closer look shows that numbers of the form a.b have been
truncated to 2 decimal places (as is set in the regional settings),
but numbers of the form a.bexp-c haven't. I don't know if there is
logic behind this or not. But... a) I wouldn't expect Access to truncate ANY numbers when writing to a
csv file REGARDLESS of the regional settings for number
representatio n. To do so is dangerous and makes Access behave
differently on different machines. Since a floating point number is stored in a binary floating format thatcannot be represented exactly as a decimal number, there is no way to exportit as decimal numeric text without approximation of some kind.
correct, floating points aren't exact. they are very close though.
for example, idl uses 32 bit or 64 bit representations of floats and
double precisions. This gives a very high approximation. MS Access
writing 0.09808088 as 0.09 is certainly NOT due to the imprecise nature
of floating points!!! Are you saying because floating pints are
imprecise then we can expect a 10% change in some floats?

b) IF it decides to truncate numbers on writing, I would expect it to
round the numbers off, rather than just truncate. Otherwise the
answer is not just less accurate, but ABSOLUTELY WRONG.
Yes, rounding would be better, but it is in the nature of floating point typesto be "wrong", we should not have expectations of how they will be directlyexported.
See above, floats are wrong but VERY precise especially for numbers but
exceedingly large or small. 0.09808088 is not very close to 0.09.
This is not a floating pointr problem. Or at least I hope not. I
thought this was a bug in MS-Access exporting certain numbers. Are you
saying I can only expect Access to be within 10% when using real
numbers?

c) IF it decides to truncate numbers to 2 decimal places, I would
expect it to truncate ALL numbers, so as to minimise confusion.

If you expect exported numeric data to be accurate, store it in an accuratenumeric format such as Long or Currency. Otherwise, if you have a particularexpectation of how you want your numbers exported, create a query that usesFormat to convert the numbers to text in your preferred way, and export that.If you do, the export will do what you specified.

I have never before met a program which writes out a number such as
0.09808088 as 0.09 and had this put down to the 'imprecise nature' of
floating point numbers.

Re LONG or Currency, I want my numbers stored and written out as
floating points to a high degree of accuracy. Why is MS-Access
incapable of doing this. And does it only reatain this shoddy of level
of accuracy within the code or just when writing out?

[rlayberry]

>So, you are new to database concepts, and that is no problem! (you got
a

whole new world to learn!!). Perhaps you are from a old time system thatused punched cards, or a old computer system that relied on data order. Textfiles for example preserve order of the data placed in them..but data indatabase has no such preserve.
I am actually new to databases. I understand the points about whether
they have an intrinsic order or not. My point is that the MS
implementation does have an order. It is the order which it displays
them in. It also has an order which it writes them out in. I dont
mind how it chooses these orders, I just would have liked them to be
the same for debugging purposes. I have 15 million entries. If I
arrange such that the line I want to examine more closely is in the top
few tens of lines, then when i write out i want that to be preserved.

The problem is that some numbers in the second column have been
truncated. A closer look shows that numbers of the form a.b have been truncated to 2 decimal places (as is set in the regional settings),
but numbers of the form a.bexp-c haven't. I don't know if there is
logic behind this or not. But...

Again, this is issue of computers..and how numbers are resented. "real"values in a computer are actually a appromaxaton. If you use integer values(or so called whole values), then a computer can do a much better job. integer, long, and the currency data types in ms-access are actually storedas whole numbers..and thus not subject to rounding...or approximate errors.If you are doing any financial business software...you better learn thislesson in a hurry.
I am doing an application which requires real numbers represented as
floats. I am aware that floating points are by their inherent nature
imprecise, but MS Aceess writing out 0.08715292 as 0.08 is not a
problem of this nature. If it was, I wouldn't be able ever to use any
microsoft products. This is a 10% error due to the way Microsoft
stores floating points!!!! I use IDL which stores floats as 32 or 64
bits. They would still be 'imprecise' but damn better than 10% out!!

However, you are obviously new to computers..and again are not aware of howthey work. The following little piece of code
from ms-access will produce the following put out...and will be a surprse.Read the code first..and imainge what the output will
look like...
How patronising. Thankfully not new to computers, just relatively new
to the squalid and bug infested world of Microsoft products.

1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1
yes. well done. we all know that floats are imprecise. Access wrote
my float of 0.08715292 as 0.08. Is this not too imprecise for you? It
is for me.

So, if you are going to use a database, or in fact use computers for any forwork, it likely is a good idea that you do some reading up on how they work.If you don't, then you will be like a first year doctor trying to treatpatients..bu t not yet know what you are doing. Without some education, andsome skills on your side..you will find many pitfalls and many surprises asto how computers work.

I would rather say, if you are going to use computers, use Unix and use
non-MS products if you want better than 10% accuracy on your floating
point numbers!

[rlayberry]

>So, you are new to database concepts, and that is no problem! (you got
a

whole new world to learn!!). Perhaps you are from a old time system thatused punched cards, or a old computer system that relied on data order. Textfiles for example preserve order of the data placed in them..but data indatabase has no such preserve.
I am actually new to databases. I understand the points about whether
they have an intrinsic order or not. My point is that the MS
implementation does have an order. It is the order which it displays
them in. It also has an order which it writes them out in. I dont
mind how it chooses these orders, I just would have liked them to be
the same for debugging purposes. I have 15 million entries. If I
arrange such that the line I want to examine more closely is in the top
few tens of lines, then when i write out i want that to be preserved.

The problem is that some numbers in the second column have been
truncated. A closer look shows that numbers of the form a.b have been truncated to 2 decimal places (as is set in the regional settings),
but numbers of the form a.bexp-c haven't. I don't know if there is
logic behind this or not. But...

Again, this is issue of computers..and how numbers are resented. "real"values in a computer are actually a appromaxaton. If you use integer values(or so called whole values), then a computer can do a much better job. integer, long, and the currency data types in ms-access are actually storedas whole numbers..and thus not subject to rounding...or approximate errors.If you are doing any financial business software...you better learn thislesson in a hurry.
I am doing an application which requires real numbers represented as
floats. I am aware that floating points are by their inherent nature
imprecise, but MS Aceess writing out 0.08715292 as 0.08 is not a
problem of this nature. If it was, I wouldn't be able ever to use any
microsoft products. This is a 10% error due to the way Microsoft
stores floating points!!!! I use IDL which stores floats as 32 or 64
bits. They would still be 'imprecise' but damn better than 10% out!!

However, you are obviously new to computers..and again are not aware of howthey work. The following little piece of code
from ms-access will produce the following put out...and will be a surprse.Read the code first..and imainge what the output will
look like...
How patronising. Thankfully not new to computers, just relatively new
to the squalid and bug infested world of Microsoft products.

1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1
yes. well done. we all know that floats are imprecise. Access wrote
my float of 0.08715292 as 0.08. Is this not too imprecise for you? It
is for me.

So, if you are going to use a database, or in fact use computers for any forwork, it likely is a good idea that you do some reading up on how they work.If you don't, then you will be like a first year doctor trying to treatpatients..bu t not yet know what you are doing. Without some education, andsome skills on your side..you will find many pitfalls and many surprises asto how computers work.

I would rather say, if you are going to use computers, use Unix and use
non-MS products if you want better than 10% accuracy on your floating
point numbers!

[Steve Jorgensen]

On 9 Dec 2004 01:15:14 -0800, rl*******@hotma il.com wrote:

Not really stupid. A relational database does not specify a physical

order

for data. If you want data exported in a particular order, use a query

and

export that.

A relational database may not specify an order, but the Microsoft
implementati on does, in 2 ways. Firstly the order it defaults to
displaying it and secondly the order it writes it out. I would have
expected these to be the same for debugging purposes.

Well, to keep arguing the point, Access is not guaranteed to represent the
records in any particular order on display unless you sort the datasheet and
save changes. Add some rows, or compact the database, and they could come up
in a different order. When you do sort the datasheet, you still are really
just saving a setting for the datasheet view of that table, not for the table
itself.

Sure, it makes sense that the export routine might should look at the saved
settings for the datasheet view, and use them for export, but how far should
it go with that? Should it see your lookup fields, and export the value taken
from the combo boxes for those fields? That said, I'm mostly agreeing with
you on this point.

The problem is that some numbers in the second column have been
truncated. A closer look shows that numbers of the form a.b have been
truncated to 2 decimal places (as is set in the regional settings),
but numbers of the form a.bexp-c haven't. I don't know if there is
logic behind this or not. But...a) I wouldn't expect Access to truncate ANY numbers when writing to a
csv file REGARDLESS of the regional settings for number
representati on. To do so is dangerous and makes Access behave
differentl y on different machines.

Since a floating point number is stored in a binary floating format

that

cannot be represented exactly as a decimal number, there is no way to

export

it as decimal numeric text without approximation of some kind.

correct, floating points aren't exact. they are very close though.
for example, idl uses 32 bit or 64 bit representations of floats and
double precisions. This gives a very high approximation. MS Access
writing 0.09808088 as 0.09 is certainly NOT due to the imprecise nature
of floating points!!! Are you saying because floating pints are
imprecise then we can expect a 10% change in some floats?

I'm saying that a lot of complex assumptions have to be made about how to
decide a text representation of a floating point number, and whatever you get
is bound to be very rough unless you format it as text yourself using a query.
Access can't know what your field means in context, so it can't know the best
way to format it. I'm guessing the decision was made to format numbers within
a certain range of magnitude near 1 as having 2 digits to the right of the
decimal point.

c) IF it decides to truncate numbers to 2 decimal places, I would
expect it to truncate ALL numbers, so as to minimise confusion.

If you expect exported numeric data to be accurate, store it in an

accurate

numeric format such as Long or Currency. Otherwise, if you have a

particular

expectation of how you want your numbers exported, create a query that

uses

Format to convert the numbers to text in your preferred way, and

export that.

If you do, the export will do what you specified.

I have never before met a program which writes out a number such as
0.09808088 as 0.09 and had this put down to the 'imprecise nature' of
floating point numbers.

Re LONG or Currency, I want my numbers stored and written out as
floating points to a high degree of accuracy. Why is MS-Access
incapable of doing this. And does it only reatain this shoddy of level
of accuracy within the code or just when writing out?

The problem is in the nature of the concepts of precision and accuracy and
which expectations to try to preserve without a knowledge of context. It is
true that floating point numbers are very "precise", but "accuracy" is
measured with respect to a fixed point interpretation, so if you want a number
with an accuracy of 0.01, then representing 0.024 as 0.02 is accurate, though
it's not very precise.

If, on the other hand, you try to represent a floating point number with all
the precision available, chances are that the number you enter as 0.018 will
come up as something like 1.7999999999999 999e-2 which simply defies a
different expectation of many users. Besides, that representation still is
not truly accurate because chances are that binary fraction has no exact
decimal representation, so home many repeating 9s to show before giving up?
In any case, reimporting the number will probably not result in the exact same
stored value. Should numbers that close to a magnitude of 1 really be shown
as floating point? If not, how should that affect how many digits to show now
that non-significant leading zeroes are included?

I think it's simply to be expected that exporting a floating point number with
no contextual knowledge on the part of Access is bound to be wierd, and that's
to be expected. Only you can supply the contextual understanding and write a
query that formats the numbers the way you want them to be formatted.

[rlayberry]

> I'm saying that a lot of complex assumptions have to be made about
how to

decide a text representation of a floating point number, and whatever you get is bound to be very rough unless you format it as text yourself using a query. Access can't know what your field means in context, so it can't know the best way to format it. I'm guessing the decision was made to format numbers within a certain range of magnitude near 1 as having 2 digits to the right of the decimal point.
I agree it is not a simple issue, turning a floating point into an
ascii text. There are better ways of doing it, however, than to turn
an 0.089 into a 0.08 (10% error) without some sort of warning, and to
only do it selectively so it isn't obvious. (ie not to 1.245678E7 type
numbers)
The problem is in the nature of the concepts of precision and accuracy and which expectations to try to preserve without a knowledge of context. It is true that floating point numbers are very "precise", but "accuracy" is measured with respect to a fixed point interpretation, so if you want a number with an accuracy of 0.01, then representing 0.024 as 0.02 is accurate, though it's not very precise.
it should retain the accuracy with which it is internally stored. when
you do the export wizard it shows you how it will export it (retaining
the significant points) then lops them off for the output. If it was
to do something else, the wizard should show that. It is after all the
representation of how your data will be exported!!
If, on the other hand, you try to represent a floating point number with all the precision available, chances are that the number you enter as 0.018 will come up as something like 1.7999999999999 999e-2 which simply defies a
different expectation of many users. Besides, that representation still is not truly accurate because chances are that binary fraction has no exact decimal representation, so home many repeating 9s to show before giving up? In any case, reimporting the number will probably not result in the exact same stored value. Should numbers that close to a magnitude of 1 really be shown as floating point? If not, how should that affect how many digits to show now that non-significant leading zeroes are included?
such that the number and its truncated version are accurate to within
some margin (<<10%)!
I think it's simply to be expected that exporting a floating point number with no contextual knowledge on the part of Access is bound to be wierd, and that's to be expected. Only you can supply the contextual understanding and write a query that formats the numbers the way you want them to be formatted.

Unfortunately this wouldn't be true of any other code I have ever used.
Floating numbers may be cast or rounded or whatever, but to have
'some' of their values changed by up to 10% with no warning! The
problem is, most people who use Access dont have such a knowledge of
it. If most people write out as ascii, have a look at the wizard and
it looks ok then get an answer out of those results which have been
decimated by the exporting process then I would regard this as a bug
even if a lot of die-hard MS fans think it is a logical implementation.
I would love someone to show me an equivalent example in Unix!!

I haven't been using MS for long, but a simple operation such as
exporting as text, I would not 'expect that to be weird'. I would
expect the code to do something sensible or tell me. Not something
stupid and tell me it is ok (via the wizard).

russ

[David W. Fenton]

rl*******@hotma il.com wrote in
news:11******** **************@ c13g2000cwb.goo glegroups.com:

How patronising. Thankfully not new to computers, just relatively
new to the squalid and bug infested world of Microsoft products.

Look, write a query with a sort order and all the numbers formatted
to the same number of decimal places.

Export *that* and you'll have reliable results.

In other words, the reason you're not getting what you want is
because you're not doing it properly. You're mistaking your personal
ignorance for bugs.

Perhaps that would explain the patronizing reactions.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tim Marshall]

rl*******@hotma il.com wrote:

Read the code first..and imainge what the output will
look like...

How patronising. Thankfully not new to computers, just relatively new
to the squalid and bug infested world of Microsoft products.

Jesus Christ, Albert took the time to write you stuff in an attempt to
HELP you that is not in anyway patronizing.

When you come to a new piece of software, there is a what? HELLO, a
learning curve!

Stop taking out your frustrations on the people here. Especially people
trying to help you.

I question your ability anyway when you have to post twice for every
post here.

PS - poor craftsmen blame their tools.

Now go ahead and write your very predictable response. See if you can
post it only once.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto