I’ve created a GradeBook in Access. It’s for a specific purpose here at my office and I want to avoid details - not to keep secrets, just to avoid confusion. Generally speaking, the database tracks how well employees recruit people to come to presentations.
Anyway, I’m having a specific problem:
In one of my reports, I calculate a Grade based on three variables:
[ul]
[li]Shows: The number of recruits that showed up[/li][li]Recruits: The total number recruited to show up[/li][li]Cancels: The number that let us know - in advance - that they weren’t going to show up[/li][/ul]
In other words, if I recruited 10 people, 3 cancelled and one was a no-show, then S=6, R=10 and C=1. The calculation says:
Grade = S / (R - C)
We subtract Cancels from Recruited in the denominator so that Recruiters are not penalized if a Recruit lets us know - in advance - that they can’t make it to the presentation.
Can you guess the problem? If everyone that I recruit cancels in advance, then the denominator is zero and the calculation results in an #Error.
I’d like to change the definition to say something like:
IF ([recruited]-[cancels]=0) THEN grade: 0 ELSE grade: [Shows]/([recruited]-[cancels])
Is that clear? I want to set grade to zero% if all recruits cancel. Once again, the Calculated Variable “Grade” is defined in the query which the Report runs. Is it possible to do what I want? I can’t find any IF/THEN syntax in the Expression Builder.
I don’t think Iif will work in this case, because it actually evaluates both TrueValue and FalseValue, regardless of the truth value of Expression. So if R-C = 0, then it will still fail with a “divide by zero” error.
Your best bet, then, would probably be to create a function in the report’s module - call it, say, CalcGrade, and use a regular VBA If-Then-Else-End If statement there to perform the evaluation - and then put a reference to it in the Control Source property of the control, as follows: =CalcGrade(arguments). (Alternatively, put the CalcGrade function in a regular module, refer to it in the appropriate field of the query, and then just set the Control Source of the report control equal to that query field.)
Sorry. You’re way over my head. I use Access, but I’m still learning. I’m much more comfortable in Excel, when I can calculate circles around most people.
Click on the Modules tab in your database, create a new module and paste this code into the module window:
Public Function CalcGrade(Recruited, Cancels, Shows As Integer) As Double
If Recruited = Cancels Then CalcGrade = 0
Else
CalcGrade = Shows / (Recruited - Cancels)
End If
End Function
Then you can just use Grade = CalcGrade([recruited],[cancels],[shows]) in your code.
I see no reason why what JeffB put wouldn’t work. In a similar example, I am calculating batting average for a player in an Access query.
My formula is:
Avg: IIf([AB]=0,0,[H]/AB])
If a player has zero at bats (a relief pitcher for instance) then the average will show .000 (based on the formatting I chose.) If a player has at bats, then it will calculate the batting average as stated in the formula. Unless I am completely overlooking something here, I think RedNaxela is making this too difficult.
Interesting… Access’ online help explicitly warns against using Iif in such a case, for the reason I mentioned: it will end up producing a “divide by zero” error if the denominator equals 0. Yet it seems to work for Mullinator and the OP, and I just ran a quick test using a query and found that it works too.
Well, then, my apologies for making this more complicated than necessary!
Despite the fact that your solution is apparently overkill for the problme at hand, it was still great to learn how to do it a more sophisticated way. I’m just starting to use Access after having been a Paradox user for years, and I find your example very enlightening. Thanks.
