Division by Zero Error

[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.

 

[John Spencer]

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]

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

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

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.

 

[Calvin Willman]

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.

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

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

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.

 

[John Spencer]

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.

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

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

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.