Zero Percent in Access

[Question]

I have written report in access, and instead of the report showing "0%" when
there is zero data, it displays "NUM." How do I get it to show "0%?"

 

[Klatuu]

Is is a cacluated text box?
How is it formatted?

 

[Question]

Yes, I believe it is a calculated text box. It is formatted as a percentage.
The percentage works if the number it is calculating is anything other than
zero. It is only when the calculation results in 0 that NUM shows up.

 

[Klatuu]

I can't seem to recreate the problem. could you send me what is in the
Control Source property of the text box, please.

 

[Question]

Here you go...
=[Sum Of 2003 - 10:30 AM]/[Sum Of 2003 Supply]

 

[Klatuu]

Okay, I don't know for sure if this will do it, but whenever there is a
divide calculaton, there is the possibility that the divisor could be zero.
I would try this"

=Cdbl([Sum Of 2003 - 10:30 AM])/Iif(Cdbl([Sum Of 2003 Supply]) =
0,1,Cdbl([Sum Of 2003 Supply]))

That will insure you get no divide by zero errors. However, based on your
NUM problem, it may be that one or the other of the fields are not numeric.
Check to see what the data types of [Sum Of 2003 - 10:30 AM] and [Sum Of 2003
Supply] are. If they are not numeric, then you could convert them in your
calculation:

=[Sum Of 2003 - 10:30 AM]/Iif([Sum Of 2003 Supply] = 0,1,[Sum Of 2003 Supply])

If that doesn't fix it, then you may have to write a function to refer to in
your query that checks for Nulls and empty strings and converts them to 0.

here is a line of code that will check them, but it is not the complete
function.

=Iif(IsNull([MyField]) or [MyField] = "",0, [MyField])

Here you go...
=[Sum Of 2003 - 10:30 AM]/[Sum Of 2003 Supply]

I can't seem to recreate the problem. could you send me what is in the
Control Source property of the text box, please.

 

[Question]

No it didn't work. I appreciate your trying to help. I just can't figure
out how to write the "if statement" to just show 0 when you divide 0 into 0.
I am stumped!

Okay, I don't know for sure if this will do it, but whenever there is a
divide calculaton, there is the possibility that the divisor could be zero.
I would try this"

=Cdbl([Sum Of 2003 - 10:30 AM])/Iif(Cdbl([Sum Of 2003 Supply]) =
0,1,Cdbl([Sum Of 2003 Supply]))

That will insure you get no divide by zero errors. However, based on your
NUM problem, it may be that one or the other of the fields are not numeric.
Check to see what the data types of [Sum Of 2003 - 10:30 AM] and [Sum Of 2003
Supply] are. If they are not numeric, then you could convert them in your
calculation:

=[Sum Of 2003 - 10:30 AM]/Iif([Sum Of 2003 Supply] = 0,1,[Sum Of 2003 Supply])

If that doesn't fix it, then you may have to write a function to refer to in
your query that checks for Nulls and empty strings and converts them to 0.

here is a line of code that will check them, but it is not the complete
function.

=Iif(IsNull([MyField]) or [MyField] = "",0, [MyField])

Here you go...
=[Sum Of 2003 - 10:30 AM]/[Sum Of 2003 Supply]

I can't seem to recreate the problem. could you send me what is in the
Control Source property of the text box, please.

:

Yes, I believe it is a calculated text box. It is formatted as a percentage.
The percentage works if the number it is calculating is anything other than
zero. It is only when the calculation results in 0 that NUM shows up.

:

Is is a cacluated text box?
How is it formatted?

:

I have written report in access, and instead of the report showing "0%" when
there is zero data, it displays "NUM." How do I get it to show "0%?"

 

[Klatuu]

You cannot divide anything by 0. Even if you divide 0 by 0, you will get an
error
(run time 6 Overflow to be exact). 0 / 1 is 0. What I sent you will work.
You may need to check the syntax to be sure I did not make an error there. I
know this method works because I use it all the time.

x= Number to Divide / Iif(Number to Divide BY = 0, 1, Number to Divide By)
what varialbes, text box values, or field values you use will determine the
exact syntax you need.

No it didn't work. I appreciate your trying to help. I just can't figure
out how to write the "if statement" to just show 0 when you divide 0 into 0.
I am stumped!

Okay, I don't know for sure if this will do it, but whenever there is a
divide calculaton, there is the possibility that the divisor could be zero.
I would try this"

=Cdbl([Sum Of 2003 - 10:30 AM])/Iif(Cdbl([Sum Of 2003 Supply]) =
0,1,Cdbl([Sum Of 2003 Supply]))

That will insure you get no divide by zero errors. However, based on your
NUM problem, it may be that one or the other of the fields are not numeric.
Check to see what the data types of [Sum Of 2003 - 10:30 AM] and [Sum Of 2003
Supply] are. If they are not numeric, then you could convert them in your
calculation:

=[Sum Of 2003 - 10:30 AM]/Iif([Sum Of 2003 Supply] = 0,1,[Sum Of 2003 Supply])

If that doesn't fix it, then you may have to write a function to refer to in
your query that checks for Nulls and empty strings and converts them to 0.

here is a line of code that will check them, but it is not the complete
function.

=Iif(IsNull([MyField]) or [MyField] = "",0, [MyField])

Here you go...
=[Sum Of 2003 - 10:30 AM]/[Sum Of 2003 Supply]

:

I can't seem to recreate the problem. could you send me what is in the
Control Source property of the text box, please.

:

Yes, I believe it is a calculated text box. It is formatted as a percentage.
The percentage works if the number it is calculating is anything other than
zero. It is only when the calculation results in 0 that NUM shows up.

:

Is is a cacluated text box?
How is it formatted?

:

I have written report in access, and instead of the report showing "0%" when
there is zero data, it displays "NUM." How do I get it to show "0%?"