zero decimal in a report field

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

Ad

M

Val(1.1) = 1.1
Val(1.0) = 1

J

John Spencer

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.

G

Guest

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!

Ad

J

John Spencer

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!

ksmith@gbnrtc.org 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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.