Dividing by zero ..overflow

[chris]

How do I stop this from happening. I have an access 2000
database with a table storing my football conference
standings. There are non-league and overall fields. My
problem is I have a query setup to show me the results:
Win, Loss (overall); Win, Loss(conference games) and the
winning percentages for both.

My problem is, since there are a few teams that haven't
played conference games my query is getting an overflow
error. Can any help me get this to output a zero in this
percentage field. Currently I am adding up all the games
played by each team so zero divided by zero needs to be
zero instead of my error (or overflow, etc).

Here is my broken down code for that one problem field.

SELECT Sum([Std_GliacWin])/Sum([Std_GLIACgame]) AS
GLIACWinPercent, SchoolTbl.SchoolName
FROM SchoolTbl LEFT JOIN StandingsTbl ON
SchoolTbl.SchoolCode = StandingsTbl.Std_TeamID
GROUP BY SchoolTbl.SchoolName
ORDER BY Abs(Sum([Std_GliacWin])/Sum([Std_GLIACgame]))
DESC;

I tried using ABS before the "Sum([" without any luck.

I hope someone can help me out.
Thanks in advance.
Chris

 

[Douglas J. Steele]

SELECT IIf(Sum([Std_GLIACgame]) <> 0,
Sum([Std_GliacWin])/Sum([Std_GLIACgame]), 0) AS
GLIACWinPercent, SchoolTbl.SchoolName
FROM SchoolTbl LEFT JOIN StandingsTbl ON
SchoolTbl.SchoolCode = StandingsTbl.Std_TeamID
GROUP BY SchoolTbl.SchoolName
ORDER BY IIf(Sum([Std_GLIACgame]) <> 0,
Sum([Std_GliacWin])/Sum([Std_GLIACgame]), 0)
DESC;

 

[chris]

OMG, THANK YOU SO MUCH!!!! Doug that was so
quick, I appreciate your helping me out in this
pinch.

Can you recommend any good websites that could
have examples like this to help me out in the
future with other problems like this?

Thanks again!!
Chris

-----Original Message-----
SELECT IIf(Sum([Std_GLIACgame]) <> 0,
Sum([Std_GliacWin])/Sum([Std_GLIACgame]), 0) AS
GLIACWinPercent, SchoolTbl.SchoolName
FROM SchoolTbl LEFT JOIN StandingsTbl ON
SchoolTbl.SchoolCode = StandingsTbl.Std_TeamID
GROUP BY SchoolTbl.SchoolName
ORDER BY IIf(Sum([Std_GLIACgame]) <> 0,
Sum([Std_GliacWin])/Sum([Std_GLIACgame]), 0)
DESC;

--
Doug Steele, Microsoft Access MVP

How do I stop this from happening. I have an access 2000
database with a table storing my football conference
standings. There are non-league and overall fields. My
problem is I have a query setup to show me the results:
Win, Loss (overall); Win, Loss(conference games) and the
winning percentages for both.

My problem is, since there are a few teams that haven't
played conference games my query is getting an overflow
error. Can any help me get this to output a zero in this
percentage field. Currently I am adding up all the games
played by each team so zero divided by zero needs to be
zero instead of my error (or overflow, etc).

Here is my broken down code for that one problem field.

SELECT Sum([Std_GliacWin])/Sum([Std_GLIACgame]) AS
GLIACWinPercent, SchoolTbl.SchoolName
FROM SchoolTbl LEFT JOIN StandingsTbl ON
SchoolTbl.SchoolCode = StandingsTbl.Std_TeamID
GROUP BY SchoolTbl.SchoolName
ORDER BY Abs(Sum([Std_GliacWin])/Sum([Std_GLIACgame]))
DESC;

I tried using ABS before the "Sum([" without any luck.

I hope someone can help me out.
Thanks in advance.
Chris

.