Division Question

  • Thread starter Thread starter Gary F Shelton
  • Start date Start date
G

Gary F Shelton

Planned vs Actual

I have some date that shows me how much was planned to be produced and how
much was acutally produced. I have been able to write a division equation of
Planned divided by Actual... No problem...

Now I want to write a new field in the query that will have text that can
identify records that return me some text that stated Over, Perfect, Under...
Any ideas?
 
Type an expression like this into the Field row in query design:
Result: IIf([Actual]=0, Null, [Planned] / [Actual])

Then in the next column, enter something like this in the field row (as one
line):
Comment: IIf([Result] Between 0.999 And 1.001, "Perfect",
IIf([Result] < 0, "Under", IIf([Result] > 0, "Over", Null)))

The first expression avoids the division-by-zero error, and yields a
fraction. In case there could be a rounding error, the 2nd expression
ignores anything that is out by only 1/1000 and makes it perfect.
 
Back
Top