Minor calculation issue.

[Chelsea7]

Hello,

If have a question about calculating percentages. Below is the code I used for a form and report. Everything seems to work fine until the percentage equals 20%. When its at 20% the stats reads FAILED. As long as the percentage is over 20%, it will read PASSED. I'm not sure what I need to ensure that if it reads 20% the Status will read PASSED. I do have an '>=' operator as you can see below.

On the Report

[Textstat]
Control Source
=IIf([status]>=[percentage],"FAILED","PASSED")

[Percentage]
Control Source
=IIf([countofcountofroute]=0,"0",[countofcountofroute]/[countofroute1])
---------------------------------------------------
In VBA Mode Code

Case "November"
Me.Text5.Value = "20% OR MORE TO PASS FOR NOVEMBER"
Me.Text7.Value = 20 / 100

--------------------------------------------------
Sample output

ROUTES | YTD TOTAL |20% > | BLANCE | STATUS
66 13 20% 53 Failed


What am I missing?

Thanks. I hope I was clear.

 

[dhookom]

First, I don't mix the return data type from IIf(). Consider changing the "0" to 0. Typically the denominator is compared with 0, not the numerator since your IIf() serves to purpose. Shouldn't the code be

=IIf([countofroute1]=0,0,[countofcountofroute]/[countofroute1])

Beyond that, I have no idea what your record source fields are or what you are attempting to do. If you have more code, please share it.

Duane
Hook'D on Access
MS Access MVP

 

[alvechurchdata]

Have you got some sort of rounding error here?

13/66 = 0.1969697

and that's less than 20%.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Chelsea7]

So what's the best way to round it up to make it 20%?

 

[PHV]

=IIf(countofroute1=0,0,Round(countofcountofroute/countofroute1,0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Chelsea7]

I used the =IIf(countofroute1=0,0,Round(countofcountofroute/countofroute1,0)) but it only turns everything to 0%

I think it has something to do with this result;

13/66 = 0.1969697

Is there a way to round this up so that it changes to 20%? These fractions are a problem. Maybe ensure that the result doesn't come out to be a fraction.

I another one that reads 20%. However, this one reads correctly as Passed. The way I need it to do. Here's the calculation;

9/44 = 0.204545

Of course it's at 20%. So I would need a formula to round it up to at least 0.20.

 

[alvechurchdata]

Your equation gives the fraction 0.1969697 so of course this rounds down to zero. Try multiplying the expression by 100 to get a percentage.

Better still, do it in two lines of code so that you can debug:

perCent = 100 * countofcountofroute/countofroute1
=IIf(countofroute1=0,0,Round(perCent,0))

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[SimonSellick]

If CountOfCountOfRoute and CountOfRoute1 are integers (and from the names, I would guess that they are), then you should do the multiplication before the division, that is:

perCent = (100 * CountOfCountOfRoute) / CountOfRoute1

Also, if you want an integer result but you need it rounded, you could multiply the intermediates by 10, add 5 (assuming that 19.5 should round to 20) then divide by 10 again:

perCent = ((1000 * CountOfCountOfRoute) / ((10 * CountOfRoute) + 5)) / 10

HTH

 

[traingamer]

Your example is indeed under 20%, so it should fail given your specification.

If you really want it to pass with a number that rounds to 20%, you could just set your pass percentage to 19.5 (or .195).

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard