Number format resulting from an aggregate query

[Sajit]

I get numbers formatted as,

27.0000005960464
41.8999988585711

resulting from an aggregate query. I tried setting the format of the field
to General, Fixed and made the decimal place to 1. It still appears the same.
I am thinking that the summation gets done after it has done the formatting.

 

[Michel Walsh]

The formatting is generally only applied when the data is to be displayed, a
little bit like makeup over the skin. The computation is generally done in a
binary format (not on strings). Using a Currency data type can help, since
it is a (scaled) integer. A Decimal may also help, but you still can get
rounding error, even with those data type, since one third, even if you
multiply it by a scale of 10 to the power of 32, is still truncated (in this
case, at the 33rd decimal).

You can still try to format the result with few digits after the decimal
delimiter (I use three digits, here):


? Format( 27.1000005960464, "#.000")
27.100
? Format( 27.299999995677, "#.000")
27.300


Hoping it may help,
Vanderghast, Access MVP

 

[Dennis]

Does it matter ?
If you use this query for a form or report, then just format the results
there.

 

[Klatuu]

Dennis is correct. Do the formatting where the number is used, either in the
report or in the form. If you are using the query to export to an exteranl
format, then use the Format function in the query around the calculation.
For example:
Format(Sum([SomeField]/[AnotherField]), "#.00")
will return the number rounded to two digits.

 

[Sajit]

If the formatting is applied before display. It should have done, when
previewing the query. It does not happen. The decimal are still visible.

With the #.# option, an empty record is displayed with a '.' (w/o the single
quotes) and integer figure sums also get a .0 at the end.

I would like to see the number with a decimal if there is a decimal place to
display within the decimal setting. If not it should be as an integer.
Is this yet another bug. Can not MS patch this up.
--
Sajit
Abu Dhabi

The formatting is generally only applied when the data is to be displayed, a
little bit like makeup over the skin. The computation is generally done in a
binary format (not on strings). Using a Currency data type can help, since
it is a (scaled) integer. A Decimal may also help, but you still can get
rounding error, even with those data type, since one third, even if you
multiply it by a scale of 10 to the power of 32, is still truncated (in this
case, at the 33rd decimal).

You can still try to format the result with few digits after the decimal
delimiter (I use three digits, here):


? Format( 27.1000005960464, "#.000")
27.100
? Format( 27.299999995677, "#.000")
27.300


Hoping it may help,
Vanderghast, Access MVP

 

[Sajit]

I am exporting the result to Excel. But then, I have to again format each of
the cells (there are several rows of data) in Excel to trim off the decimals.

 

[Michel Walsh]

Since you asked for general format, you get what the system decided to be
appropriate, 15 digits for the whole number. You should have asked to only 2
digits past the decimal delimiter, as example. And indeed, the format IS
AFTER the computation. Where do you got that it is applied BEFORE?

The Format statement may define four parts, each part delimited by a semi
colon:


? Format( 27.1000005960464, "#.000;(#.000);0;N.A."), Format( null ,
"#.000;-#.000;zero;N.A.")
27.100 N.A.


The parts are: if positive, if negative, if zero, if null. Here, I used two
different formats: for the first format, I used ( ) around a negative
number, 0,000 if the value is zero, and N.A if the value is null. In the
second format, I use a simple negative sign for negative number, instead of
( ), the text zero if the value is 0, and,again, N.A if the value is null.

To be a "bug" it has to be a result that does not follow the published
specification (by opposition to NOT be whatever ANY someone may have in mind
about what the result should be). In this case, when you have a number
without decimal part (decimal part = 0), since numbers are generally
right-aligned, it is more useful to have the zero as fillers in order to get
a column of number properly 'aligned':

2.90
7.45
8.00


rather than what you got without the filling zeros (and right aligned):

2.9
7.45
8



So, no, it is not 'a bug', but what the specs said it should be.



Vanderghast, Access MVP

If the formatting is applied before display. It should have done, when
previewing the query. It does not happen. The decimal are still visible.

With the #.# option, an empty record is displayed with a '.' (w/o the
single
quotes) and integer figure sums also get a .0 at the end.

I would like to see the number with a decimal if there is a decimal place
to
display within the decimal setting. If not it should be as an integer.
Is this yet another bug. Can not MS patch this up.

 

[Sajit]

Point taken, Michel.

I knew of the format statement but was only trying out the drop down format
options that is there with the properties box.