If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Number format resulting from an aggregate query
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. -- Sajit Abu Dhabi |
#2
|
|||
|
|||
Number format resulting from an aggregate query
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" wrote in message ... 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. -- Sajit Abu Dhabi |
#3
|
|||
|
|||
Number format resulting from an aggregate query
Does it matter ?
If you use this query for a form or report, then just format the results there. "Sajit" wrote: 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. -- Sajit Abu Dhabi |
#4
|
|||
|
|||
Number format resulting from an aggregate query
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. -- Dave Hargis, Microsoft Access MVP "Sajit" wrote: 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. -- Sajit Abu Dhabi |
#5
|
|||
|
|||
Number format resulting from an aggregate query
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 "Michel Walsh" wrote: 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" wrote in message ... 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. -- Sajit Abu Dhabi |
#6
|
|||
|
|||
Number format resulting from an aggregate query
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. -- Sajit Abu Dhabi "Dennis" wrote: Does it matter ? If you use this query for a form or report, then just format the results there. "Sajit" wrote: 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. -- Sajit Abu Dhabi |
#7
|
|||
|
|||
Number format resulting from an aggregate query
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 a 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 "Sajit" wrote in message ... 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 "Michel Walsh" wrote: 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" wrote in message ... 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. -- Sajit Abu Dhabi |
#8
|
|||
|
|||
Number format resulting from an aggregate query
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. -- Sajit Abu Dhabi "Michel Walsh" wrote: 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 a 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 "Sajit" wrote in message ... 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 "Michel Walsh" wrote: 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" wrote in message ... 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. -- Sajit Abu Dhabi |
Thread Tools | |
Display Modes | |
|
|