Division by Zero Error

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

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
 
C

Calvin Willman

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

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.

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

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.

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Division by Zero Error 1
"Division by Zero" Question 2
Division By Zero error 2
Divide By Zero Error 2
Divide by Zero error 3
"Division by zero", but... 2
division by zero 5
division by zero-PLEASE HELP 5

Top