MS Access division-by-zero error

I have a VB6 baseball simulation program that has an MS Access connection to store data in tables. One of the things the program does is aggregate pitching statistics from all games and calculate things like ERA, WHIP, BB per 9 innings, K per 9 innings etc. for each pitcher's data.

If a pitcher pitches 0 innings----such as coming into the game and walking a guy, and then being immediately replaced, it causes a Division-by-Zero error because his TIP (total innings pitched) = zero. Well, you can't divide by 0 or you get an error. I'm using the IIF IsNull method in the SQL statement.... but it doesn't work. There has to be some change to this statement that will make it work---I think I just have it setup wrong.

Here's the statement:

SqlString = "Select Pitcher, Team, SUM([K]) As Ks, SUM([BB]) As BBs, SUM([HITS]) As H, SUM(
) As HRs, SUM([RUNS]) As R, SUM([IP]) As TIP, (SUM([K]) / (TIP / 9)) As [Ks/9], (SUM([BB]) / (TIP / 9)) As [BBs/9], (SUM([HITS]) / (TIP / 9)) As [H/9], (SUM(
) / (TIP / 9)) As [HRs/9], (SUM([K]) /  [BBs]) As [K/BB], ((IIf(IsNull([R]), 0, [R]) / IIf(IsNull([TIP]), .01, [TIP])) * 9) As ERA, ((IIf(IsNull([BBs]), 0, [BBs]) + IIf(IsNull([H]), 0, [H]))  / IIf(IsNull([TIP]), .01, [TIP])) AS WHIP FROM Pitching GROUP BY Team, Pitcher"

Notice the places where I divide by TIP, this has to be the issue..... any help is REALLY appreciated; many thanks.
LVL 4
jazjefAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try using something like this

iif([divisor]=0,0,[dividend]/[divisor])
Gerwin Jansen, EE MVETopic Advisor Commented:
There are more possible divbyzero's in your statement, like these:  (SUM([K]) / (TIP / 9))

If TIP is zero then TIP / 9 is also zero, causing a divbyzero error.
Nick67Commented:
I'm using the IIF IsNull method in the SQL statement....
IIf(IsNull([TIP]), .01, [TIP])) * 9)


So you are substituting 0.01 for zero.

Then just do it in the underlying table
Don't permit innings pitched to be zero, make it be 0.01 and voila, no grief


How does MLB handle it?
Usually they have a minimum required number
Drew Hutchinson now holds the highest ERA ever in one season at Fenway at 18+
But they calculate that on minimum 9 innings pitched

You have many unhandled TIP

"Select Pitcher,
Team,
SUM([K]) As Ks,
SUM([BB]) As BBs,
SUM([HITS]) As H,
SUM(
) As HRs,
SUM([RUNS]) As R,
SUM([IP]) As TIP,
--You can try juicing TIP here, to avoid problems below

(SUM([K]) / (TIP / 9)) As [Ks/9], --unhandled

(SUM([BB]) / (TIP / 9)) As [BBs/9],  --unhandled

(SUM([HITS]) / (TIP / 9)) As [H/9],  --unhandled

(SUM(
) / (TIP / 9)) As [HRs/9],  --unhandled
 (SUM([K]) /  [BBs]) As [K/BB],
((IIf(IsNull([R]), 0, [R]) / IIf(IsNull([TIP]), .01, [TIP])) * 9) As ERA,
((IIf(IsNull([BBs]), 0, [BBs]) + IIf(IsNull([H]), 0, [H]))  / IIf(IsNull([TIP]), .01, [TIP])) AS WHIP
FROM Pitching GROUP BY Team, Pitcher"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
I prefer something like:

IIF(NZ([Tip], 0) = 0, NULL, Sum([K])/(TIP/9)) as [Ks/9]

to replace:

(SUM([K]) / (TIP / 9)) As [Ks/9]

This ensures that if there are no full innings pitched, there are no pitching statistics for that game.  Which would be different than the zeros that would be returned by several of the other recommendations.
jazjefAuthor Commented:
Nick67 is right.... The reality is that unless a pitcher meets a certain number of innings pitched, then writing a value such as .33 (a third of an inning pitched) works fine when it comes to recording such minimal pitcher data in the table. Once a pitcher meets the minimum criteria of IP---such as 27---then the value of the .33 is essentially 'absorbed' into the pitching statistics to a point where it's accurate to within about .003 or less. Until Nick67's suggestion, I never really thought about how that small value would be absorbed as a pitcher pitched more and more innings....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.