Round function returns too many decimals in query (1 Viewer)

rsf

Registered User.
Local time
Yesterday, 22:07
Joined
Sep 18, 2009
Messages
15
I am using the Round function in a query to display a number field in a table to 2 decimal places, but it returns a number with about 10 decimal places. Please could anyone tell me why this is?
Example:
In a table there is a field called 'Num' which is of number type 'Single'. A value in this field is 39.553.
I want to show the values in 'Num' to 2 decimal places, so I use the query

SELECT Table1.Num, Round([Num],2) AS Rounded
FROM Table1;

But this query returns 39.549999237 when Num=39.55, 39.553 etc.
Why doesn't it return 39.55?!
I can get round this by changing the Round expression to Round(CDbl([Num]),2) but shouldn't the simpler version work?
Thanks for any help!
Steve
 

rsf

Registered User.
Local time
Yesterday, 22:07
Joined
Sep 18, 2009
Messages
15
The datatype is Number (field size Single). The decimal places property of the field is 'Auto', but that should be irrelevant, shouldn't it?

Thanks
 

namliam

The Mailman - AWF VIP
Local time
Today, 06:07
Joined
Aug 11, 2003
Messages
11,696
it does seem odd, and with my db I dont have any issues...
 

jjturner

Registered User.
Local time
Today, 05:07
Joined
Sep 1, 2002
Messages
386
did some digging and found this thread, which you may be interested in...

I would imagine that precision issues with these datatypes, used in conjunction with the Round function can yield very unexpected results, as you're seeing.

It may be that changing your datatype is the best option (to Currency, for instance)

In any case, it sounds as if the internal routines involved with Round leave something to be desired.

I'll dig about some more and see if I can find out anything else...
 

rsf

Registered User.
Local time
Yesterday, 22:07
Joined
Sep 18, 2009
Messages
15
Many thanks for pointing me towards that thread; I never knew that about single and double types, I suspect that's the reason nailed... certainly learned something there!
Cheers
Steve
 

jjturner

Registered User.
Local time
Today, 05:07
Joined
Sep 1, 2002
Messages
386
In case you're really interested, here is the low-down on Single precision floating-point numbers
 

namliam

The Mailman - AWF VIP
Local time
Today, 06:07
Joined
Aug 11, 2003
Messages
11,696
Personaly never use Singles :(, what else would you expect from a married man :)
 

jjturner

Registered User.
Local time
Today, 05:07
Joined
Sep 1, 2002
Messages
386
namliam - glad to hear you're keeping honest :D

After reading up on these Singles datatypes, it might be more accurate to call them "Single imprecision floating-point" numbers

What a troublesome lot, these Singles (and I'll have to grudgingly admit that I'm one me-self :p )
 

rsf

Registered User.
Local time
Yesterday, 22:07
Joined
Sep 18, 2009
Messages
15
Many thanks for your help, much appreciated. It's certainly made me suspicious of using Single and Double types.
 

Users who are viewing this thread

Top Bottom