# MS Access Gurus: Help me avoid undefined numbers! **URL:** \ **Category:** Factual Questions\ **Created:** [May 6, 2003, 3:04pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408 "2003-05-06T15:04:49Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![sdimbert](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sdimbert/32/20204_2.png) [@sdimbert](https://boards.straightdope.com/u/sdimbert)\ **Post date:** [May 6, 2003, 3:04pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/1 "2003-05-06T15:04:49Z")
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. ---
**Author:** ![sdimbert](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sdimbert/32/20204_2.png) [@sdimbert](https://boards.straightdope.com/u/sdimbert)\ **Post date:** [May 6, 2003, 3:06pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/2 "2003-05-06T15:06:10Z")
(Whups! Forgot to set E-mail notification.) ---
**Author:** ![JeffB](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jeffb/32/188_2.png) [@JeffB](https://boards.straightdope.com/u/JeffB)\ **Post date:** [May 6, 2003, 3:24pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/3 "2003-05-06T15:24:04Z")
**sdimbert** , the Access function you are looking for is “IIf” (two "I"s). The format is: ```auto IIf( , TrueValue, FalseValue ) IIf( [recruite]-[cancels]=0, 0, [Shows]/([recruited]-[cancels] ) ``` ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 6, 2003, 3:27pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/4 "2003-05-06T15:27:28Z")
Grade = Iif((R-C=0),0,S/(R-C)) ---
**Author:** ![RedNaxela](https://avatars.discourse-cdn.com/v4/letter/r/ad7895/32.png) [@RedNaxela](https://boards.straightdope.com/u/RedNaxela)\ **Post date:** [May 6, 2003, 3:39pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/5 "2003-05-06T15:39:02Z")
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.) ---
**Author:** ![sdimbert](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sdimbert/32/20204_2.png) [@sdimbert](https://boards.straightdope.com/u/sdimbert)\ **Post date:** [May 6, 2003, 3:50pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/6 "2003-05-06T15:50:01Z")
> [@](#): > > \*Originally posted by RedNaxela \* > \*\*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.) \*\* :rubs eyes:: 😕 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. 🙂 Can you explain a little slower? ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 6, 2003, 4:09pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/7 "2003-05-06T16:09:36Z")
Click on the _Modules_ tab in your database, create a new module and paste this code into the module window: ```auto 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. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [May 6, 2003, 4:10pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/8 "2003-05-06T16:10:38Z")
Actually, I’m not sure if integer type was right for the passed parameters… ---
**Author:** ![Mullinator](https://avatars.discourse-cdn.com/v4/letter/m/c2a13f/32.png) [@Mullinator](https://boards.straightdope.com/u/Mullinator)\ **Post date:** [May 6, 2003, 4:12pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/9 "2003-05-06T16:12:22Z")
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. ---
**Author:** ![sdimbert](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/sdimbert/32/20204_2.png) [@sdimbert](https://boards.straightdope.com/u/sdimbert)\ **Post date:** [May 6, 2003, 4:20pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/10 "2003-05-06T16:20:39Z")
Thank you, everyone! 😃 In the end, the Iif Expression worked. Thanks, all, for working it out with me. ---
**Author:** ![RedNaxela](https://avatars.discourse-cdn.com/v4/letter/r/ad7895/32.png) [@RedNaxela](https://boards.straightdope.com/u/RedNaxela)\ **Post date:** [May 6, 2003, 8:23pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/11 "2003-05-06T20:23:54Z")
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! ---
**Author:** ![chorpler](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/chorpler/32/2888_2.png) [@chorpler](https://boards.straightdope.com/u/chorpler)\ **Post date:** [May 6, 2003, 11:15pm UTC](https://boards.straightdope.com/t/ms-access-gurus-help-me-avoid-undefined-numbers/173408/12 "2003-05-06T23:15:06Z")
> [@](#): > > \*Originally posted by RedNaxela \* > \*\*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.