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

Query results in needless/extra zeros

P: n/a
I have a datasheet subform that is based off an ADO recordset. All is
fine excpet the query results are displayed oddly. Rather than
displaying the results as say 95.43, it displays it as
95.430000000000000. Obviously I don't want these extra zero's. I have
tried limiting the decimal places in the properties of the textbox in
which the results are displayed, but no effect. Hopefully one of you
have an idea. Thanks for your help!
-Jeremy

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


P: n/a
"Jeremy" <sl********@hotmail.com> wrote in message news:11**********************@z14g2000cwz.googlegr oups.com...
I have a datasheet subform that is based off an ADO recordset. All is
fine excpet the query results are displayed oddly. Rather than
displaying the results as say 95.43, it displays it as
95.430000000000000. Obviously I don't want these extra zero's. I have
tried limiting the decimal places in the properties of the textbox in
which the results are displayed, but no effect. Hopefully one of you
have an idea. Thanks for your help!

format([myfield], "#.00")
Nov 13 '05 #2

P: n/a
Thanks for the response Wind, but I have tried that and no effect. I
think it has something to do with the SQL query as I am using "Cast (my
query/calculation) as Decimal". Which I have to do to get the query to
work. For example:

strSQL = strSQL & " ROUND((CAST(COUNT(CASE WHEN VINInputSource = 'V'
THEN 1 ELSE NULL END) as decimal)/Cast(COUNT(CASE WHEN VINInputSource
Is NOT NULL THEN 1 ELSE NULL END) as Decimal)*100),2) VINScan,"
etc, etc...

The sql statements that do not use "Cast...As Decimal" will format
fine. Any ideas?

Nov 13 '05 #3

P: n/a

"Jeremy" <sl********@hotmail.com> wrote in message news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks for the response Wind, but I have tried that and no effect. I
think it has something to do with the SQL query as I am using "Cast (my
query/calculation) as Decimal". Which I have to do to get the query to
work. For example:

strSQL = strSQL & " ROUND((CAST(COUNT(CASE WHEN VINInputSource = 'V'
THEN 1 ELSE NULL END) as decimal)/Cast(COUNT(CASE WHEN VINInputSource
Is NOT NULL THEN 1 ELSE NULL END) as Decimal)*100),2) VINScan,"
etc, etc...

The sql statements that do not use "Cast...As Decimal" will format
fine. Any ideas?


I have never heard of cast ... what does that do?
Nov 13 '05 #4

P: n/a

"Jeremy" <sl********@hotmail.com> wrote in message news:11**********************@z14g2000cwz.googlegr oups.com...
Thanks for the response Wind, but I have tried that and no effect. I
think it has something to do with the SQL query as I am using "Cast (my
query/calculation) as Decimal". Which I have to do to get the query to
work. For example:

strSQL = strSQL & " ROUND((CAST(COUNT(CASE WHEN VINInputSource = 'V'
THEN 1 ELSE NULL END) as decimal)/Cast(COUNT(CASE WHEN VINInputSource
Is NOT NULL THEN 1 ELSE NULL END) as Decimal)*100),2) VINScan,"
etc, etc...

The sql statements that do not use "Cast...As Decimal" will format
fine. Any ideas?


It is always really useful to break it down into many little steps and check each step along the way... rather than having one long
statement. Maybe Cast is fine, but the content is wrong, maybe Cast does not work itself...
Nov 13 '05 #5

P: n/a
I still haven't figured this one out....

As to your question, CAST is a SQL command that converts data from one
type to another, in this case I am converting an integer to a decimal
so that I can get a percentage. It is similar to VB commands like
CBool, CDate, CInt, etc... Simply converting from one data type to
another. I just don't understand why Access, or ADO in this case,
returns tons of needless zero's.

I have tried eliminating the CAST command and WALLAH! It works, no
extra zero's. But then I get a whole number and don't get the two
decimal places nessecary to form a percentage.

Nov 13 '05 #6

P: n/a

"Jeremy" <sl********@hotmail.com> wrote in message news:11**********************@l41g2000cwc.googlegr oups.com...
I still haven't figured this one out....

As to your question, CAST is a SQL command that converts data from one
type to another, in this case I am converting an integer to a decimal
so that I can get a percentage. It is similar to VB commands like
CBool, CDate, CInt, etc... Simply converting from one data type to
another. I just don't understand why Access, or ADO in this case,
returns tons of needless zero's.

I have tried eliminating the CAST command and WALLAH! It works, no
extra zero's. But then I get a whole number and don't get the two
decimal places nessecary to form a percentage.


I think it may be a rounding issue on conversion (guess). I would just enclose the whole thing in format(#.00) or times it by one
hundred and then divide it again.

Anyway, I would not use SQL to convert numbers, I would do that in Access itself, as you will have more control over it.

In this situation I would be practical rather than theoretical.

HTH

- Nicolaas
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.