Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-18-2009, 01:39 AM   #1
rsf
Newly Registered User
 
Join Date: Sep 2009
Posts: 15
Thanks: 3
Thanked 3 Times in 3 Posts
rsf is on a distinguished road
Round function returns too many decimals in query

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 is offline   Reply With Quote
Old 09-18-2009, 04:08 PM   #2
jjturner
Newly Registered User
 
Join Date: Aug 2002
Location: Boston, MA
Posts: 387
Thanks: 0
Thanked 2 Times in 2 Posts
jjturner is on a distinguished road
Re: Round function returns too many decimals in query

That sounds odd. What datatype is Num?
jjturner is offline   Reply With Quote
Old 09-21-2009, 06:58 AM   #3
rsf
Newly Registered User
 
Join Date: Sep 2009
Posts: 15
Thanks: 3
Thanked 3 Times in 3 Posts
rsf is on a distinguished road
Re: Round function returns too many decimals in query

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

rsf is offline   Reply With Quote
Old 09-21-2009, 07:08 AM   #4
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 798 Times in 785 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Round function returns too many decimals in query

it does seem odd, and with my db I dont have any issues...
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 09-21-2009, 09:54 AM   #5
jjturner
Newly Registered User
 
Join Date: Aug 2002
Location: Boston, MA
Posts: 387
Thanks: 0
Thanked 2 Times in 2 Posts
jjturner is on a distinguished road
Re: Round function returns too many decimals in query

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...
jjturner is offline   Reply With Quote
Old 09-21-2009, 10:26 AM   #6
rsf
Newly Registered User
 
Join Date: Sep 2009
Posts: 15
Thanks: 3
Thanked 3 Times in 3 Posts
rsf is on a distinguished road
Re: Round function returns too many decimals in query

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
rsf is offline   Reply With Quote
Old 09-21-2009, 05:59 PM   #7
jjturner
Newly Registered User
 
Join Date: Aug 2002
Location: Boston, MA
Posts: 387
Thanks: 0
Thanked 2 Times in 2 Posts
jjturner is on a distinguished road
Re: Round function returns too many decimals in query

In case you're really interested, here is the low-down on Single precision floating-point numbers

jjturner is offline   Reply With Quote
Old 09-22-2009, 02:07 AM   #8
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 798 Times in 785 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Round function returns too many decimals in query

Personaly never use Singles , what else would you expect from a married man
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 09-22-2009, 08:56 AM   #9
jjturner
Newly Registered User
 
Join Date: Aug 2002
Location: Boston, MA
Posts: 387
Thanks: 0
Thanked 2 Times in 2 Posts
jjturner is on a distinguished road
Re: Round function returns too many decimals in query

namliam - glad to hear you're keeping honest

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 )
jjturner is offline   Reply With Quote
Old 09-22-2009, 11:24 AM   #10
rsf
Newly Registered User
 
Join Date: Sep 2009
Posts: 15
Thanks: 3
Thanked 3 Times in 3 Posts
rsf is on a distinguished road
Re: Round function returns too many decimals in query

Many thanks for your help, much appreciated. It's certainly made me suspicious of using Single and Double types.

rsf is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[ACCESS 97] Search multiple keywords bodylojohn Forms 58 03-26-2012 06:19 PM
Function in Query won't return Value catbeasy Modules & VBA 3 08-20-2009 01:18 PM
[SOLVED] Problem exporting a query using Transfertext method seattlerick Modules & VBA 4 09-11-2007 07:35 AM
#Error with Function in query bradles Queries 5 11-12-2006 08:47 AM
Using a function as query criteria R2D2 Queries 1 11-24-2003 01:06 AM




All times are GMT -8. The time now is 11:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World