MSAccess中的大量错误 [英] massive bug in MSAccess

问题描述

嗨

我发现在MSAccess中看起来像一个巨大的错误。

当我在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

我将表保存为CSV文件...我得到以下

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

订单不同，这不是错误，只是微软的愚蠢

实施。

问题是第二列中的某些数字已经被截断了
。仔细观察一下，表格ab的数字已经被截断到2个小数位（在区域设置中设置），

但形式为a的数字。 bexp-c没有。我不知道这背后是否有

逻辑。但是......

a）我不希望Access在写入

csv文件时截断任何数字，无论区域设置为数字

代表。这样做是危险的，并使Access在不同的机器上表现得不同。

b）如果它决定在写入时截断数字，我会期望它为

将数字四舍五入，而不是截断。否则

答案不仅不太准确，而且绝对错误。

c）如果它决定将数字截断为2位小数，我会

期望它截断所有数字，以尽量减少混淆。

有没有人有任何意见？我不知道这是不是真的是一个b / b
的bug，或者它背后有一些微软的逻辑。如果它是一个bug，那么

一般会让我非常不信任MS Access。

Russ

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

推荐答案

2004年12月8日07:02:15 -0800， rl ******* @ hotmail.com 写道：

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

嗨

我发现在MSAccess中看起来像一个巨大的bug。

当我在访问表中有以下内容时

1996 0.08715292
1996 12.98775
1996 1.987653
1996 0.09476294
1996 0.1012828
1996 1.25012 E-06
1996 0.08638828
1996 0.09060681
1996 0.09808088
2010 128.9784
2020 1.568336E-05

我保存表格为一个CSV文件...我得到以下

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
订单不同，这不是错误，只是微软的愚蠢/>实施。


不是很愚蠢。关系数据库没有为数据指定物理订单

。如果您想要按特定顺序导出数据，请使用查询和

导出。

问题是第二列中的某些数字已被截断。仔细观察表明a.b形式的数字已被截断为2位小数（如区域设置中所设置的），
但a.bexp-c形式的数字还没有。我不知道这背后是否存在逻辑。但是......

a）我不希望Access在写入
csv文件时截断任何数字，无论数字
表示的区域设置如何。这样做很危险，并使Access在不同的计算机上表现不同。


由于浮点数以二进制浮动格式存储，因此
不能完全表示为十进制数，因此无法导出

它作为十进制数字文本，没有某种近似值。

b）如果它决定在写入时截断数字，我会期望它将数字四舍五入，而不是而不仅仅是截断。否则
答案不仅不太准确，而且绝对错误。


是的，舍入会更好，但它具有浮点类型的性质

是错误的，我们不应该期望它们将如何直接导出

导出。

c）如果它决定将数字截断为2位小数，我会希望它截断所有数字，所以尽量减少混淆。


对于浮点数，你事先并不知道什么是重要的
。如果你的所有数字都是.000001等级，并且所有数字都有10

有效数字，并且你随意将所有数据都舍入为.0001，那么你

已经丢弃 - 所有所有数字的有效数字，使它们全部

为零。这就是为什么你的计算器理解科学记数法的原因。
有没有人有任何意见？我不知道这是不是真的是一个错误，或者它背后有一些微软的逻辑。如果它是一个错误，它通常会让我非常不信任MS Access。

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

如果您希望导出的数字数据准确，请将其存储在准确

数字格式，如Long或Currency。否则，如果您对输出数字的方式有特殊的期望，请创建一个使用

格式的查询，以便按照您喜欢的方式将数字转换为文本，以及导出。

如果这样做，导出将按照您的指定进行。

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.

>顺序是不同的，这不是一个错误，只是微软的愚蠢的

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

实现。


实际上，在过去的穿孔卡，甚至是一些基于旧文件的数据系统中，数据被认为是订购的。


因此，来自穿孔卡片的数据，甚至那些旧的卷轴到卷轴系统的数据，或者你在旧电影中看到的
实际上确实保留了订单。

但是，今天，如果你使用Oracle，My-Sql，Sybase和几乎所有现代的数据库系统，磁盘系统上的数据顺序是未知的。实际上，您的应用程序可能正在从系统中获取数据，而且可能有10个用户正在添加和删除记录。 （并且，

更有趣的是，ms-access是一个关系数据库，你正在查看的数据实际上可能来自TWO
不同的计算机，两者的记录合并为一个视图....

关系数据库可以做到这一点!!）。因为现代数据库系统非常流畅，并且允许多个用户添加和删除数据（而不是zester年份的旧打孔卡），那么订单数据不可能是
维护......甚至不知道它在数据库系统中的位置。关于唯一可以肯定的是，要求数据引擎检索您需要的数据。该算法以及计算机如何检索单个记录

是书籍的主题，并且如何从现代数据库引擎中检索数据，存在许多不同的方案。但是，有一点可以肯定我们

确实知道这些方案并没有保留它输入的数据的顺序。（而且，如果你有10个人将数据添加到文件中...

订单应该放入数据吗？


如果每个人都维护数据的顺序（所以他们可以

回去检查他们在白天完成的工作？）。


或者，


我们是否应该按照添加的每条新记录订购数据？（如果你有15美元，那么人们添加记录......这是任何价值的订单了吗？可能不会）。


那么，我们应该使用哪种？由每个人自己的订单，或者每个人/ b $ b人混合添加记录？


真的，正确的答案是按照你需要的顺序简单地订购数据。数据引擎构建者很久以前放弃了保留数据的顺序

输入..它再也没什么价值了。所以，你不能成功e />
现代系统中的数据顺序（这是数据库101！）。如果您需要

每个用户返回的数据，那么您将通过


订购数据库。用户名添加了报价

然后按顺序排序

TimeStamp（或进入的日期和时间）。


而且，如果你不关心输入数据的10个人......但是护理

关于添加记录的实际订单，然后您必须按日期和进入时间订购

数据。 （并且，假设您将此设计为

您的应用程序）。在你的情况下，可能使用自动编号主键字段

就足够了（每增加一条记录就增加一个......而且我不认为

你需要一个时间戳在这里）。


此外，当一个用户开始输入一些数据时会发生什么......但是另一个用户开始输入一些数据...但是更快......并尽快完成。你可以看到，按物理磁盘或文件顺序维护数据的顺序是一个很老的，过时的概念。


所以，你是数据库概念的新手，这没问题！ （你有一个全新的世界要学习!!）。也许你来自旧时系统，使用穿孔卡，或依赖数据顺序的旧计算机系统。文本

文件例如保留放在其中的数据的顺序..但是数据在
数据库中没有这样的保留。

问题是第二列中的某些数字已被截断。仔细观察表明a.b形式的数字已被截断为2位小数（如区域设置中所设置的），
但a.bexp-c形式的数字还没有。我不知道这背后是否存在逻辑。但是......

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

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

再一次，这是计算机的问题......以及数字如何受到憎恨。 真实的
计算机中的
值实际上是一个批准。如果你使用整数值

（或所谓的整数值），那么计算机可以做得更好。

整数，长整数和货币数据类型以ms为单位-access实际上存储了

作为整数...因此不受四舍五入...或近似错误。

如果您正在做任何金融业务软件......你最好快点学习这个

课。


然而，你显然是新手电脑......再次不知道怎么回事

他们工作。来自ms-access的以下一小段代码

将产生以下信息......并且将是一个反复出现。

首先阅读代码..并且imainge是什么输出将是

看起来像......


Public Sub TestAdd（）


Dim MyNumber As Single

Dim i As Integer


对于i = 1到10

MyNumber = MyNumber + 1.01

Debug.Print MyNumber

下一页我

结束子


这是以上的实际输出：


1.01

2.02

3.03

4.04

5.05

6.06

7.070001

8.080001

9.090001

10.1


你可以看到只有7次添加后...已经进行了舍入


如果我们在上面的末尾添加以下代码：

如果MyNumber = 10.1 = True那么


msgbox"数字是10.1"


其他

msgbox这个数字是其他的东西

endif


以上将实际产生：


数字是别的东西


所以，如果你要使用数据库，或者实际上使用计算机对于

的工作，你可能会对它们的工作方式进行一些阅读。这可能是个好主意。

如果你不这样做，那么你就会像第一年的医生试图治疗患者......但是还不知道你在做什么。没有一些教育，并且你需要一些技能......你会发现许多陷阱和很多惊喜，因为计算机的工作原理是什么。


我想在开车的时候可以说同样的事情，或者那个第一个学习并发现存在于空气中的细菌的b
药用学生，但是你看起来不行他们。


在汽车上工作，或在人身上工作，或在电脑上工作需要花费很多技能，而且需要花费很多钱。很多学习。你想在这里学习一些其他的东西

将被视为无能......或者更糟糕的是你的工作，或者有诉讼等等。

对你不利。并且，如果使用计算机的任何后果只是你个人的时间......那么你应该自己去了解这些东西是如何运作的。

喜欢在汽车上工作，学习医学，或学习计算机......

结果在个人满意度方面是巨大的。


你不可能在一天内成为一名医生..同样适用于使用

计算机......你需要一些时间来学习所有这些

的东西......

-

Albert D. Kallal（访问MVP）

加拿大艾伯塔省埃德蒙顿
PL ***************** @ msn.com
http://www.attcanada.net/~kallal.msn

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

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

>不是很愚蠢。关系数据库没有为数据指定物理

命令

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

。如果您希望以特定顺序导出数据，请使用查询
andexport。


关系数据库可能没有指定订单，但Microsoft

实现有两种方式。首先是它默认显示的顺序为
显示它，其次是它写出来的顺序。我希望

预计这些在调试时是相同的。

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.

问题在于第二列中的数字已被截断。仔细观察表明a.b形式的数字已被截断为2位小数（如区域设置中所设置的），
但a.bexp-c形式的数字还没有。我不知道这背后是否存在逻辑。但是......
a）我不希望Access在写入
csv文件时截断任何数字，无论数字
表示的区域设置如何。这样做很危险，并使Access在不同的计算机上表现不同。
由于浮点数以二进制浮点格式
存储，不能精确地表示为十进制数，因此没有办法将
exportit作为十进制数字文本而不用某种近似值。


正确，浮点数不准确。它们非常接近。

例如，idl使用32位或64位浮点表示和

双精度。这给出了非常高的近似值。 MS Access

写0.09808088为0.09肯定不是由于不精确的性质

的浮点数！你是说因为浮动品脱是不确定的，那么我们可以预期一些花车会有10％的变化吗？


b）如果它决定在写作时截断数字，我希望它可以将数字四舍五入，而不仅仅是截断。否则
答案不仅不太准确，而且绝对错误。


是的，四舍五入会更好，但是浮动
点类型的性质是错误的，我们不应该期望他们将如何成为
直接出口。


如上所述，浮标是错误的，但非常精确，特别是对于数字而言，

非常大或小。 0.09808088不是非常接近0.09。

这不是浮动指针问题。或者至少我希望不是。我认为这是MS-Access导出某些数字的错误。你是否使用真正的

数字，我只能期望Access在10％以内吗？


c）如果它决定要将数字截断为2位小数，我会希望它截断所有数字，以便最大限度地减少混淆。

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

如果您希望导出数字数据到准确，将其存储为
精确数字格式，例如Long或Currency。否则，如果你有一个
特定的期望你想要你的数字导出，创建一个查询
usesFormat将数字转换为你喜欢的方式的文本，
导出。如果你这样做，导出将按照你指定的方式进行。

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.

我以前从未遇到一个程序写出一个数字，如

0.09808088为0.09并且这已经归结为

浮点数的不精确性。


Re LONG或Currency，我希望我的数字存储和写入以
浮动指向高精度。为什么MS-Access

无法做到这一点。并且只是在代码中或者在写出时才重新获得这种精确度等级的精确度吗？

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?

这篇关于MSAccess中的大量错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！