zero decimal in a report field

  • Thread starter Thread starter ksmith
  • Start date Start date
K

ksmith

I am doing a report that shows calculated values of hours per week.

The report takes the sum of all hours on day and gives me the total at
the bottom of the report

ex:
Tues
2
3
2.5
2.1
total 9.6

The problem is that I want to format the total to read only a decimal
with there is a number other than zero present. Meaning if the total
equals just 6, I want the report to display just 6 and not 6.0. My
text box control source reads =sum([tues]), which gives me the sums of
all the inputed tues values. Is there a way to format the text box
that if the value after the decimal is 0 drop it and if the value is 1
to 9 keep it. I only want one decimal place.

Thanks
 
I think that you would have to use some VBA code to do this. Perhaps
something like this

IIF(Right(Format(TheNumber,"#.#"),1)=".",Format(TheNumber,"0"),Format(TheNumber,"0.0"))

Using the Format function will force your number into a string. So, you may
have to reconvert it back to a number to get the total. Also, the alignment
of the displayed value will probably change.
 
AFAIK, if you leave the decimal places setting of the total field at Auto, it
will adjust itself accordingly. Or am I being way too simplistic and missing
the point entirely? If I have, ignore me, and I'll get back in my box!
 
My assumption was that the user had actual values like
1; 1.5; 1.91; 2.0005; 8.000000893
and would like those displayed as
1; 1.5; 1.9; 2; 8

I gave one possible solution.

Another possible (untested) solution
--round the value to one decimal place and then use your suggestion using
AUTO decimal places.

kt2902 said:
AFAIK, if you leave the decimal places setting of the total field at Auto,
it
will adjust itself accordingly. Or am I being way too simplistic and
missing
the point entirely? If I have, ignore me, and I'll get back in my box!

[email protected] said:
I am doing a report that shows calculated values of hours per week.

The report takes the sum of all hours on day and gives me the total at
the bottom of the report

ex:
Tues
2
3
2.5
2.1
total 9.6

The problem is that I want to format the total to read only a decimal
with there is a number other than zero present. Meaning if the total
equals just 6, I want the report to display just 6 and not 6.0. My
text box control source reads =sum([tues]), which gives me the sums of
all the inputed tues values. Is there a way to format the text box
that if the value after the decimal is 0 drop it and if the value is 1
to 9 keep it. I only want one decimal place.

Thanks
Click to expand...
Click to expand...
 
Back
Top