Field changed from Single to Double issue..

[DN]

I have an Access 2000 DB (I think it's 2000, it's
whatever comes with Visual Studio 6.0).. Simply put, I
have a field that is of type "Single" that contains the
value 0.3. When I modify that field and make
it "Double", access automatically converts the values, as
it should, but returns 0.300001192 in the field.. I
understand that there can be precision issues sometimes
in comparisons and such, but is this NORMAL for Access to
do as well? Is there any simple work around or patch? I
suppose I can write an SQL statement to take care of
this, but that isn't something I want to do as there are
many fields. . . Thanks!

 

[Chris Mills]

I think you're missing two zeros in the precision there...

It's well-known that binary can only represent decimal to it's level of
precision.

For a table field (single), 0.30000001 (6 zeros) seems to run off the scale so
get's truncated (or in your case vice-versa). But actually a single can store
much higher precision than that (13 zeros in the example) according to tests
on a "Dim s as Single". And both a single and double in code present 0.3 as
0.3, so it's confusing isn't it?
-----
Still, I s'pose you wanted a resolution...

I think you've found a limitation of the way table conversions work. It may be
a "bug" but regardless it's a fact.

You could maybe do your conversions via a Recordset. I haven't tested that,
just that conversions in code seem to work better.

If it's critical (like for your accountant), see if one of the PRECISE formats
suits, like integer(multiplied) or currency.

It's dangerous to use "=" in comparing floating point numbers, which are
unlikely to be to infinite precision (though I see in code sample comparisons
it says "near enough" at some point. You can use >=0.3 or maybe
Format(single,"blah-blah")=0.3 or some other rounding truncation to control
the precision.

(tested A2000 and A2002)
Chris

 

[Guest]

If I look at the single field, it says "0.3", period,
not "0.30000001" or anything like that -- it was entered
by hand as 0.3 so there is no precision issue with it.

However, after the conversion from single to double, it
does say "0.3000001192" (regardless of the exact number of
zeroes after the 3). This just doesn't make sense unless
the single was also 0.3xxxxxxxxxxxx, imo, and it wasn't..
I really don't want to code anything as I've got like 200
fields in this situation and they are all doing this sort
of thing -- sigh!

 

[Guest]

After messing around today with this issue, I have found a
solution that works perfectly! Instead of having it go
from Single to Double directly, I have it go from Single
to Text and then Text to Double. I don't have to use any
program code, I just do it via access. Thanks anyways!

 

[Chris Mills]

Instead of having it go

from Single to Double directly, I have it go from Single
to Text and then Text to Double.

Ah well, you found a way! Really I was limited to confirming it appears to be
a bug in some Access processes, just as you said.

Chris

 

[Guest]

Yes, I did. I also found ANOTHER silly bug in Access
where it was giving me this "Too many fields changed"
error (not word for word). The help on the error message
was telling me that I was modifying too many fields at the
same time, even if it was only ONE, even. Just by chance,
I decided to do a "compact 'n repair" on the DB and that
fixed the problem. How annoying can this get?

 

[Chris Mills]

I don't know about that.

But it's unfortunate, for the original problem you reported, that PSS
personnel like specifically Ki Yi, seem happy to occupy seats within PSS and
not want to report easily demonstrable problems with Microsoft Access. How
warm does her ass need to be?

Chris