Tags: access, atthe, calculated, database, decimal, field, microsoft, mysql, oracle, report, sql, sum, total, values, zero

zero decimal in a report field

On Database » Microsoft Access

4,163 words with 4 Comments; publish: Fri, 06 Jun 2008 12:43:00 GMT; (25062.50, « »)

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

All Comments

Leave a comment...

  • 4 Comments
    • Val(1.1) = 1.1

      Val(1.0) = 1

      #1; Fri, 06 Jun 2008 12:45:00 GMT
    • I think that you would have to use some VBA code to do this. Perhaps

      something like this

      IIF(Right(Format(TheNumber,"#.#"),1)=".",Format(Th eNumber,"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.

      <ksmith.ms-access.itags.org.gbnrtc.org> wrote in message

      news:1133368185.726580.256800.ms-access.itags.org.g44g2000cwa.googlegr oups.com...

      >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

      >

      #2; Fri, 06 Jun 2008 12:46:00 GMT
    • 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.ms-access.itags.org.gbnrtc.org" wrote:

      > 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

      >

      #3; Fri, 06 Jun 2008 12:47:00 GMT
    • 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" <kt2902.ms-access.itags.org.discussions.microsoft.com> wrote in message

      news:95EE028D-8421-4BC1-9315-A0ECA25EC9E6.ms-access.itags.org.microsoft.com...[vbcol=seagreen]

      > 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.ms-access.itags.org.gbnrtc.org" wrote:

      #4; Fri, 06 Jun 2008 12:48:00 GMT