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.