By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

massive bug in MSAccess

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
On 8 Dec 2004 07:02:15 -0800, rl*******@hotmail.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.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.


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.
Nov 13 '05 #2

P: n/a
> 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

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


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
Nov 13 '05 #3

P: n/a
>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
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.

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?

Nov 13 '05 #4

P: n/a
>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..but 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!

Nov 13 '05 #5

P: n/a
>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..but 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!

Nov 13 '05 #6

P: n/a
On 9 Dec 2004 01:15:14 -0800, rl*******@hotmail.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
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.


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


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.7999999999999999e-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.
Nov 13 '05 #7

P: n/a
> 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.7999999999999999e-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

Nov 13 '05 #8

P: n/a
rl*******@hotmail.com wrote in
news:11**********************@c13g2000cwb.googlegr oups.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
Nov 13 '05 #9

P: n/a
rl*******@hotmail.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
Nov 13 '05 #10

P: n/a

I use my own rounding function, but you may wish to use the
internal function called "Format" to format your numbers if
you do not like the default format.

The default format is designed to be useful for naive users.
Fortunately, "Format" allows you to define other formats for
your representation of numbers.

There is explicit control of the display sort order: you can
set that in the display interface.

There is explicit control of the export sort order: you can
set that in the export source.

Fortunately, playing with the column order and sort order does
not affect the underlying data. However, you can copy and paste
from the display interface if you find that convenient.

(david)
<rl*******@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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
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
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.


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 types
to be "wrong", we should not have expectations of how they will be

directly
exported.


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

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?


Nov 13 '05 #11

P: n/a
>
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.
NO NO NO NO!!!

You can NOT assume the above. I have seen where one user edits a record..and
then when you re-load the form..the record does NOT appear in the same
position!!!

I have also seen a form designed with a sub-form to show invoice details.
MOST of the time you can rely on the order that you entered the records for
the invoice details..but again..I seen order change by just the mere fact of
editing some data. So, you statement of the following is just plain wrong:
My point is that the MS
implementation does have an order. It is the order which it displays
them in.
The above order can actually change..and is not fixed. If you add, or delete
OTHER records then what you are actually displaying in the above list..the
order can actually change on you! I seen this happen many times. Once again,
I am not sure what problem you have with the English language, but I
re-state it for you:

You can not assume order of the data as it sits on the file, it is subject
to change and therefore YOU MUST SET the order of the data. Just editing
some data..or even re-loading a form can have that data return in a
different order. You CAN NOT assume order of that data. You MUST tell the
database engine what order you want.
It also has an order which it writes them out in. I dont
mind how it chooses these orders
It does not in fact choose a order. The design of the "data buckets" and
placing of records (which are variable length) in to these data buckets (or
often referred to as disk frames) does not entail any order. As mentioned,
the designs of modern database engines did not, and do not assume order.and
therefore you cannot also assume order either.
, I just would have liked them to be
the same for debugging purposes.
Perhaps the above might have been a good idea..but as mentioned, the first
lesson you learn is not to assume data order, and you can't.
0.08715292 as 0.08 is not a problem of this nature.

Actually, if you are storing the data in a double field, then during the
export you need to "cast" the field as a double. Sql exports tend to take on
whatever data the column shows at the start. If you build a query, and in
that query you can use:

cdbl([YouFieldNameGoesHere]).

The above will ensure during the export that the number is not rounded. So,
just cast the number to the data type you want during export. You can even
use the format command if you want to fix the number of decimal places. I
you do either approach (format, or cdbl), then your data will export as you
originally have it...

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!


Actually, when Unix runs on a Intel Processor, they all use the same IEEE
representation for floating numbers. So, you will not get any difference
using Unix or Microsoft in this regards....they both use the same
representing on the Intel platform.

As much as you wish this to be a MS vs Unix thing...it is NOT!!...but once
again..your lack of understanding on this issue...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #12

P: n/a
>
0.08715292 as 0.08 is not a

problem of this nature.


To export the above...just build a collum in the query builder:

MyNubmer:format([YouFieldGoesHers],"General Number")
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #13

P: n/a
<rl*******@hotmail.com> wrote
I am actually new to databases. I
understand the points about whether
they have an intrinsic order or not.


Apparently not -- because you keep repeating that Microsoft has AN order in
which it displays and AN order in which it writes them, as though the data
were kept in a sequential file. The Microsoft implementation, of course, has
to do things in _some_ order, but that order may differ depending on the
context UNLESS you do what the software PROVIDES to ensure the order you
want... that is a Query with Sorting specified (which translates to an SQL
statement with an ORDER BY clause). Any other expectation is unrealistic.

It just doesn't do any good to argue that some software (e.g., Access)
_should_ work the way you'd like it to; if you are to succeed, expect to go
through the learning curve to learn how it DOES work, and then work WITH it
rather than complaining that it is working AGAINST you.

There is also VBA code that will allow you to closely control the order in
which you are reading the information from the Tables or Queries, and
closely control the formatting of the Output. If you choose to work with the
default, then you are stuck with what the default does FOR (or TO) you. But,
there's a long way that you can still go using Queries and Exporting to the
Text format of your choosing.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #14

P: n/a
On Thu, 09 Dec 2004 23:48:45 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:

[...]
Actually, if you are storing the data in a double field, then during the
export you need to "cast" the field as a double. Sql exports tend to take on
whatever data the column shows at the start. If you build a query, and in
that query you can use:

cdbl([YouFieldNameGoesHere]).

The above will ensure during the export that the number is not rounded. So,
just cast the number to the data type you want during export. You can even
use the format command if you want to fix the number of decimal places. I
you do either approach (format, or cdbl), then your data will export as you
originally have it...

[...]

Albert

CDbl doesn't work with the Export Text Wizard, the data is still
truncated to two decimal places.

There are two KB articles

http://support.microsoft.com/default...b;en-us;208408

for Access 2000 and

http://support.microsoft.com/default...b;en-us;153364

for Access 95, 97. Note in the second article the "Status: This
behavior is by design". This is not in the Access 2000 article anymore
[sic]!

I would say "this is by very bad design", as in general, the Export
Text Wizard is not the best piece of software I have ever seen, and it
has gotten worse since Access 2.0. The OP does have a point here, if
we abstract from his anti-ms hystrionics.

What *does* work ist to use CStr instead of CDbl, but then you
probably should modify the export specification not to quote strings,
which may interfere with other columns in the export...

Thus, Format ist the best solution for CSV export.
For fixed column export, there is no problem.

Greetings,
Matthias Kläy
--
www.kcc.ch
Nov 13 '05 #15

P: n/a
Yes...the cdbl does not work...and in fact you have to use:

MyNubmer:format([YouFieldGoesHers],"General Number")

(I had thought either one would work, and was wrong. However, I did suggest
to try both...and my 2nd soltion did work..).

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

This discussion thread is closed

Replies have been disabled for this discussion.