Formatting whole numbers and decimals differently

[RW_ECC]

I want to print the results of a query where some values are whole numbers, some have 1 decimal place, some have 0.3333333 recurring etc.
Problem is that in order to fit all the columns on a page the column width does not allow all the 0.33333... to display. So what I see is like this (there are lots of columns, I am just giving an example from one column)

891
833.5
######

I don't want to fix decimal places to 1 because then I would see 891.0 instead of 891.
How can I format the number field so that whole numbers are shown without decimal place, numbers with 1 decimal place stay like that i.e. 833.5 is fine as it is. But display something like 313.666666666667 as 313.67 rather than ##### getting put in as not enough room for all the decimal numbers.

 

[plog]

You would use the Format function (http://www.techonthenet.com/access/functions/numeric/format.php) inside a conditional statement (Iif). Inside the testing argument you would use the Int function (http://www.techonthenet.com/access/functions/numeric/int.php)

If [NumberField] equals Int([NumberField]) then format it like its an integer. If not, format it with decimal places.

 

[RW_ECC]

Is there nothing simpler that would work? Most numbers come out OK when running a query e.g. I am happy with anything that is a whole number or has 1 or 2 D.Ps e.g. 100 or 100.5 or 100.25 are all fine. But Access does not seem to like displaying something like 100.333333333.... instead of doing the "sensible" thing and truncating the number in the display e.g. to 100.3333 it shows ######## which I cannot then read or see in my printout without having extremely wide columns. In Excel the "sensible" truncation gets done in the display. And although I cannot be 100% sure, I don't think I had this problem in previous Access ?2003 version I used.

 

[pr2-eugin]

Just wondering why you have not used Round function?

? Round(891, 2)
 891 
? Round(833.5, 2)
 833.5 
? Round(833.533333333,2)
 833.53

 

[RW_ECC]

I had not realised round worked like that - thanks.

 

[pr2-eugin]

Well there will be some disadvantages of using Round.. It might Round it up, so..

? Round(23.999874, 2)
24

So if that does matter, I would reflect on plog's suggestion..