Query results in needless/extra zeros

[Jeremy]

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

[WindAndWaves]

"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")

[Jeremy]

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?

[WindAndWaves]

"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?

[WindAndWaves]

"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...

[Jeremy]

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.

[WindAndWaves]

"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