By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Strange Divide-By-Zero Error In Access 2000 Query

P: n/a
Can anyone tell why the query below would throw a divide-by-zero
error? The only reason I can see for the error happening would be if
SUM([EXP_NET]) came out to be zero, but this never happens with the
data I'm using. SOME of the values in the EXP_NET column are zero, but
the sum itself is always non-zero.

The query is:

TRANSFORM SUM([EXP_NET]-[RECOVERY])/SUM([EXP_NET])*100 AS DATA SELECT
TBL.EQUIPMENT AS EQUIPMENT,
SUM([EXP_NET]-[RECOVERY])/SUM([EXP_NET])*100 AS TOTAL FROM
REC_DEF_ANALYSIS AS TBL GROUP BY TBL.EQUIPMENT PIVOT TBL.PERIOD;

This works fine until a few zero values show up in the EXP_NET column.
I've verified that the zero values are the culprit by changing them
all to nulls, which the SUM function ignores. The really strange thing
here is that I can run the SUM formulas manually with:

SELECT SUM([EXP_NET]-[RECOVERY])/SUM([EXP_NET])*100 FROM
REC_DEF_ANALYSIS

and I don't get the divide-by zero error.

Anyone have any ideas? Thanks in advance.
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.