how do i replace "#NUM!" AND "#DIV/0!" in my reports?

[gino]

when i pull up the report sometimes text values appear because of invalid values or calculations such as "#NUM!" AND "#DIV/0!".

i don't want to confuse the user who is pulling up this report. i want to replace these text to my own personal text. is this possible.

for example, if #NUM! appears i want it to have a value of 0.

if anyone can help i would be greatful. thanks to all in advance.

 

[RpbertS]

this may help you Im not sure.
In the format event of whatever header your getting that stuff in try this:
If fieldname= "" Or IsNull(fieldname) Or IsEmpty(fieldname) then

fieldname = "0"
end if

I think something along those lines may work if not just ignore me.
Rpb

 

[Pat Hartman]

There are two problems you need to address. The first is fields that contain nulls and the second is division by zero. Whenever you perform a calculation and one of the fields involved is null, the answer is null and that turns into #NUM. Whenever you attempt to divide by zero, the result is #DIV/0 (or worse).

To eliminate the nulls, use the Nz() function: Nz(YourField,0) will return the value of YourField or O if YourField is null.

To eliminate the divide by zero error, you need to avoid the division operation when the divisor is zero or null:
IIf(Nz(YourField,0) = 0, 0, FldA/YourField)

 

[gino]

pat thanks for your help but the code seem not to work. i'm not familiar w/ nz function. could you help me out if possible?

 

[Pat Hartman]

Please post the code that isn't working.

 

[gino]

pat here is my code i got from you and i just filled them in.

IIf(Nz([SumOfS1])0) = 0, 0, FldA/[SumOfS1])

sorry pat i'm plain clueless about this one.

 

[Pat Hartman]

I changed one of the characters to a comma:
IIf(Nz([SumOfS1],0) = 0, 0, FldA/[SumOfS1])

This statement is valid but it is shown out of context. Where are you getting SumOFS1 and FldA?

 

[KellySmith]

Hi Pat,
Thanks for the information. You just solved a problem I've been wrestling with since yesteday!

 

[kkulick]

Pat,
Can you do the same type of thing in a form? Thanks.

Kris

[This message has been edited by kkulick (edited 07-21-2000).]

 

[Pat Hartman]

You could do the same calculation in an unbound control. Setting a bound control to a calculation takes a little more work.

 

[Mantispony]

Re: how do i replace "#NUM!" AND "#DIV/0!" in my reports?

Very sorry about the necro-post, but I've got an issue related to the one described above.

I've tried

IIf(Nz([Field1],0) = 0, 0, FldA/[Field1])

But that only gave me an error message.

I've also tried

NZ(Sum([Field1]*[Field2]),0

as suggested elsewhere, and edited it like this:

NZ(Sum([Field1]/[Field2]),0

but that doesn't do anything at all.

I apologize again for necro-posting, but it's fairly important (it's for my job, not school).

Thanks!!

 

[vbaInet]

Re: how do i replace "#NUM!" AND "#DIV/0!" in my reports?

I've tried

IIf(Nz([Field1],0) = 0, 0, FldA/[Field1])

But that only gave me an error message.

What is the error message? An error message is not the same as #Error.

 

[Brianwarnock]

Re: how do i replace "#NUM!" AND "#DIV/0!" in my reports?

You should always state what your error message is as we are not telepathic.

As for these
NZ(Sum([Field1]/[Field2]),0
How can that sum give a Null .

Brian

 

[Mantispony]

Re: how do i replace "#NUM!" AND "#DIV/0!" in my reports?

The problem has been solved, thanks anyway