Sql Query In Access .. Division By Zero Error

[shaju_devassy]

Kindly note i am using Access97
i want to run a create table query , The problem is Field50 has records with zeros in it also the result of Field34+Field35+Field37+Field33+Field36 too gives out zeros hence i am getting Division by zero error ..

pls suggest some alternative to solve this error so i can run this query

SELECT NRBOMBS.shipper, , Sum(NRBOMBS.Field51) AS REV1, Sum(NRBOMBS.Field50\[NRBOMBS].[Field34]+[NRBOMBS].[Field35]+[NRBOMBS].[Field37]+(NRBOMBS.Field33+NRBOMBS.Field36)) AS revenueperbox INTO custwise
FROM NRBOMBS
GROUP BY NRBOMBS.shipper
ORDER BY Sum(NRBOMBS.Field51) DESC;

Thanks very much for all your help extended

[shaju_devassy]

URGENTLY PLS HELP ME ... WITH THIS REGARDS ENABLE ME TO RUN A CREATE TABLE QUERY ..


THANKS FOR ALL HELP EXTENDED

[Shaggy Hiker]

You are using Access 97? I believe that doesn't use t-SQL, which limits your options (no IF statements). I see no obvious way to solve this, however, here are a few observations that might help: Since a division by 0 is invalid, but you are trying to do that, what does the result of such a calculation mean? Should a divide by 0 in this case be evaluated to 0? In which case, perhaps a WHERE statement could be used to exclude those cases where the divisor is 0 (or NULL).
If, on the other hand, a value of 0 could be replaced with a verry small value (or some other value) for the sake of this particular calculation, then a very ugly solution would be to do three SQL statements in a row (can you do transactions in Access SQL?). The first statement would SELECT for all with value = 0, and replace the values with the alternative value. The next would do the calculation (all 0's are gone by this point). The last would find that value and replace them with 0. I really don't like that idea, but it probably won't suit anyhow.