Division by zero when exporting to excel

M

Marco Silva

Hi. I've got a report based on a querie. On that querie I've got a column
that returns me a percentage of a value, may column formula is:
Perc_Cons_Corpos: ([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) ,and sometimes
the column [Prod_diaria_cravadeira] has the value of zero and the column
[Dif_Cons_Corpos] has a value. Translate in numbers we have this: 44/0 and
returns the value #Error.

So when I try to export this report, based on this querie that has this
field it returns this error "Division by zero".

Is possible to make the result be zero when this happens?
Regards, in advenced.
Marco
 
A

Allen Browne

Use IIf() to handle the case where the divisor is zero:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira])
 
M

Marco Silva

Hi, thanks for the quick answser. I entered this: Perc_Cons_Tampos:
IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) on the field column, but it
returns me the following error: "You may entered an operand without an
operator."

What is this?

Regards,
Marco

Allen Browne said:
Use IIf() to handle the case where the divisor is zero:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Marco Silva said:
Hi. I've got a report based on a querie. On that querie I've got a column
that returns me a percentage of a value, may column formula is:
Perc_Cons_Corpos: ([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) ,and
sometimes
the column [Prod_diaria_cravadeira] has the value of zero and the column
[Dif_Cons_Corpos] has a value. Translate in numbers we have this: 44/0 and
returns the value #Error.

So when I try to export this report, based on this querie that has this
field it returns this error "Division by zero".

Is possible to make the result be zero when this happens?
Regards, in advenced.
Marco
 
A

Allen Browne

There's a spurious bracket in there:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
[Dif_Cons_Corpos]/[Prod_diaria_cravadeira])

Of course, that's actually all on one line.

If that doesn't work, perhaps you are in a country that uses something other
than the comma as the list separator.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Marco Silva said:
Hi, thanks for the quick answser. I entered this: Perc_Cons_Tampos:
IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) on the field column, but it
returns me the following error: "You may entered an operand without an
operator."

What is this?

Regards,
Marco

Allen Browne said:
Use IIf() to handle the case where the divisor is zero:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira])

Marco Silva said:
Hi. I've got a report based on a querie. On that querie I've got a
column
that returns me a percentage of a value, may column formula is:
Perc_Cons_Corpos: ([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) ,and
sometimes
the column [Prod_diaria_cravadeira] has the value of zero and the
column
[Dif_Cons_Corpos] has a value. Translate in numbers we have this: 44/0
and
returns the value #Error.

So when I try to export this report, based on this querie that has this
field it returns this error "Division by zero".

Is possible to make the result be zero when this happens?
Regards, in advenced.
 
M

Marco Silva

Thanks Allen, it worked.

Could you check this post and see if you could give me a little help?

http://www.microsoft.com/communitie...dg=microsoft.public.access.externaldata&fltr=


Regards,
Marco.


Allen Browne said:
There's a spurious bracket in there:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
[Dif_Cons_Corpos]/[Prod_diaria_cravadeira])

Of course, that's actually all on one line.

If that doesn't work, perhaps you are in a country that uses something other
than the comma as the list separator.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Marco Silva said:
Hi, thanks for the quick answser. I entered this: Perc_Cons_Tampos:
IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) on the field column, but it
returns me the following error: "You may entered an operand without an
operator."

What is this?

Regards,
Marco

Allen Browne said:
Use IIf() to handle the case where the divisor is zero:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira])

Hi. I've got a report based on a querie. On that querie I've got a
column
that returns me a percentage of a value, may column formula is:
Perc_Cons_Corpos: ([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) ,and
sometimes
the column [Prod_diaria_cravadeira] has the value of zero and the
column
[Dif_Cons_Corpos] has a value. Translate in numbers we have this: 44/0
and
returns the value #Error.

So when I try to export this report, based on this querie that has this
field it returns this error "Division by zero".

Is possible to make the result be zero when this happens?
Regards, in advenced.
 
W

wal50

Once again, a bit of research on this site provides the answer I need.
Thanks,
wal50

Allen Browne said:
There's a spurious bracket in there:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
[Dif_Cons_Corpos]/[Prod_diaria_cravadeira])

Of course, that's actually all on one line.

If that doesn't work, perhaps you are in a country that uses something other
than the comma as the list separator.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Marco Silva said:
Hi, thanks for the quick answser. I entered this: Perc_Cons_Tampos:
IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) on the field column, but it
returns me the following error: "You may entered an operand without an
operator."

What is this?

Regards,
Marco

Allen Browne said:
Use IIf() to handle the case where the divisor is zero:

Perc_Cons_Corpos: IIf([Prod_diaria_cravadeira] = 0, 0,
([Dif_Cons_Corpos]/[Prod_diaria_cravadeira])

Hi. I've got a report based on a querie. On that querie I've got a
column
that returns me a percentage of a value, may column formula is:
Perc_Cons_Corpos: ([Dif_Cons_Corpos]/[Prod_diaria_cravadeira]) ,and
sometimes
the column [Prod_diaria_cravadeira] has the value of zero and the
column
[Dif_Cons_Corpos] has a value. Translate in numbers we have this: 44/0
and
returns the value #Error.

So when I try to export this report, based on this querie that has this
field it returns this error "Division by zero".

Is possible to make the result be zero when this happens?
Regards, in advenced.
 

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

Top