jazjef
asked on
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.
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.
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.
If TIP is zero then TIP / 9 is also zero, causing a divbyzero error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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....
iif([divisor]=0,0,[dividen