Division Question

[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?

 

[Allen Browne]

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.