Division by Zero Error

  • Thread starter Thread starter Calvin Willman
  • Start date Start date
C

Calvin Willman

I'm trying to create a calculated field, which returns the margin.

The part of the Select statement looks like this...

Sum(IIf ([TOTAL_PRICE]=0,0,
IIf([TOTAL_PROFIT]=0,0,([TOTAL_PROFIT]/[TOTAL_PRICE])*100) )) AS MARGIN

But I'm getting a Division By Zero error message still.... I thought this
should check for Zero's and just return Zero if one is found.

Can someone have a look at this please, as I've never used the IIF statement
in Access SQL Statements before.
 
Interesting, I don't think that should happen. Try rewriting the expression
as

Sum(IIf ([TOTAL_PRICE]=0 Or
[TOTAL_PROFIT]=0,0,[TOTAL_PROFIT]/IIF([TOTAL_PRICE]=0,1,[Total_Price])*100)
) AS MARGIN
 
Thanks for your reply. I'm really puzzled by it as well. I'm getting the
same error.



John Spencer said:
Interesting, I don't think that should happen. Try rewriting the
expression as

Sum(IIf ([TOTAL_PRICE]=0 Or
[TOTAL_PROFIT]=0,0,[TOTAL_PROFIT]/IIF([TOTAL_PRICE]=0,1,[Total_Price])*100)
) AS MARGIN


Calvin Willman said:
I'm trying to create a calculated field, which returns the margin.

The part of the Select statement looks like this...

Sum(IIf ([TOTAL_PRICE]=0,0,
IIf([TOTAL_PROFIT]=0,0,([TOTAL_PROFIT]/[TOTAL_PRICE])*100) )) AS
MARGIN

But I'm getting a Division By Zero error message still.... I thought this
should check for Zero's and just return Zero if one is found.

Can someone have a look at this please, as I've never used the IIF
statement in Access SQL Statements before.
Click to expand...
Click to expand...
 
I had accidently created two entries in the Query designer... one without
the IIF conditional, so it was that which was creating the error. It worked
fine.

Thanks again.

Calvin Willman said:
Thanks for your reply. I'm really puzzled by it as well. I'm getting the
same error.



John Spencer said:
Interesting, I don't think that should happen. Try rewriting the
expression as

Sum(IIf ([TOTAL_PRICE]=0 Or
[TOTAL_PROFIT]=0,0,[TOTAL_PROFIT]/IIF([TOTAL_PRICE]=0,1,[Total_Price])*100)
) AS MARGIN


Calvin Willman said:
I'm trying to create a calculated field, which returns the margin.

The part of the Select statement looks like this...

Sum(IIf ([TOTAL_PRICE]=0,0,
IIf([TOTAL_PROFIT]=0,0,([TOTAL_PROFIT]/[TOTAL_PRICE])*100) )) AS
MARGIN

But I'm getting a Division By Zero error message still.... I thought
this should check for Zero's and just return Zero if one is found.

Can someone have a look at this please, as I've never used the IIF
statement in Access SQL Statements before.
Click to expand...
Click to expand...
Click to expand...
 
If you remove the expression entirely, what happens? Do you still get the
error?

Can you post your entire SQL statement? That might point us to another area
to examine.

I'll be out of the office for a good portion of the day, so I won't be
checking this newsgroup for a several hours. Hopefully, you will be able to
solve this problem.

Calvin Willman said:
Thanks for your reply. I'm really puzzled by it as well. I'm getting the
same error.



John Spencer said:
Interesting, I don't think that should happen. Try rewriting the
expression as

Sum(IIf ([TOTAL_PRICE]=0 Or
[TOTAL_PROFIT]=0,0,[TOTAL_PROFIT]/IIF([TOTAL_PRICE]=0,1,[Total_Price])*100)
) AS MARGIN


Calvin Willman said:
I'm trying to create a calculated field, which returns the margin.

The part of the Select statement looks like this...

Sum(IIf ([TOTAL_PRICE]=0,0,
IIf([TOTAL_PROFIT]=0,0,([TOTAL_PROFIT]/[TOTAL_PRICE])*100) )) AS
MARGIN

But I'm getting a Division By Zero error message still.... I thought
this should check for Zero's and just return Zero if one is found.

Can someone have a look at this please, as I've never used the IIF
statement in Access SQL Statements before.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top