Is this a bug?

[johannes]

Create a table in Access 2000 and enter the numbers in Double:

TABLE1:

ID FIELD1
1 0.01999
2 0.001999

Then export that table to .txt format, the following is produced:

"ID" "FIELD1"
1 , 0.01
2 , 1.999e-03


This happens whether the format is default (no format), Standard or
Scientific. As you can see, the error can be up to 100% on certain numbers,
e.g. when 0.01 was produced instead of 0.01999.

 

[Allen Browne]

This is by design.

Without testing, it may be possible to work around it by creating an export
spec.

You can get around it by explicitly formatting the number as a calculated
query field, e.g.:
Format([Field1], "#,##.0.0###")

 

[johannes]

Yes I see, this will work by making the number into a text field.

But it is still bad design IMO. I only discovered the 'issue' the hard way.
Why shouldn't it produce the correct number in the first place, like e.g.
Excel does in a similar situation? Why on earth did they design it that way?

What's the purpose of getting 0.01 in stead of 0.01999 when the smaller number
0.001999 is exported correctly? It just doesn't make sense.

My fix (after the discovery) was to scale my numbers such that two digits
after the radix point would be sufficient, in case that should happen.

Johannes Andersen

This is by design.

Without testing, it may be possible to work around it by creating an export
spec.

You can get around it by explicitly formatting the number as a calculated
query field, e.g.:
Format([Field1], "#,##.0.0###")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Create a table in Access 2000 and enter the numbers in Double:

TABLE1:

ID FIELD1
1 0.01999
2 0.001999

Then export that table to .txt format, the following is produced:

"ID" "FIELD1"
1 , 0.01
2 , 1.999e-03


This happens whether the format is default (no format), Standard or
Scientific. As you can see, the error can be up to 100% on certain
numbers,
e.g. when 0.01 was produced instead of 0.01999.

 

[Guest]

It exports using your Windows number settings.

Which is the way a really helpless user would expect
to see numbers, and is probably the right way to do
it.

Since you are sophisticated enough to want your numbers
in a double format, you are also sophisticated enough to
learn how to do it. :~)

So, everybody is happy, right?

:~)

(david)

Yes I see, this will work by making the number into a text field.

But it is still bad design IMO. I only discovered the 'issue' the hard way.
Why shouldn't it produce the correct number in the first place, like e.g.
Excel does in a similar situation? Why on earth did they design it that way?

What's the purpose of getting 0.01 in stead of 0.01999 when the smaller number
0.001999 is exported correctly? It just doesn't make sense.

My fix (after the discovery) was to scale my numbers such that two digits
after the radix point would be sufficient, in case that should happen.

Johannes Andersen

This is by design.

Without testing, it may be possible to work around it by creating an export
spec.

You can get around it by explicitly formatting the number as a calculated
query field, e.g.:
Format([Field1], "#,##.0.0###")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Create a table in Access 2000 and enter the numbers in Double:

TABLE1:

ID FIELD1
1 0.01999
2 0.001999

Then export that table to .txt format, the following is produced:

"ID" "FIELD1"
1 , 0.01
2 , 1.999e-03


This happens whether the format is default (no format), Standard or
Scientific. As you can see, the error can be up to 100% on certain
numbers,
e.g. when 0.01 was produced instead of 0.01999.

 

[johannes]

It exports using your Windows number settings.

What is 'Windows number' settings? I thought this was decided by
the applications you run, e.g. Excel gets it right when you export to a
..txt file.

Which is the way a really helpless user would expect
to see numbers, and is probably the right way to do
it.

If only the export method was consistent, i.e. if it always resulted in two
digits after the radix point, then you would soon realize that this was the
way it works. However, as I have pointed out, the export result isn't
consistent. In fact in most cases you get the full precision with many digits
after the radix point; only in some cases you don't. It's not defensible as a
design.

Since you are sophisticated enough to want your numbers
in a double format, you are also sophisticated enough to
learn how to do it. :~)

Hardly relevant. You're not supposed to know all the quirks of some software
when the purpose of the software is to get the job done and save time.

So, everybody is happy, right?

:~)

Hmm not sure about that, but I had my moan. You live and learn.