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

gino

Registered User.
Local time
Today, 07:58
Joined
Mar 16, 2000
Messages
117
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

Registered User.
Local time
Today, 07:58
Joined
Feb 14, 2000
Messages
93
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

Super Moderator
Staff member
Local time
Today, 03:58
Joined
Feb 19, 2002
Messages
42,981
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

Registered User.
Local time
Today, 07:58
Joined
Mar 16, 2000
Messages
117
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

Super Moderator
Staff member
Local time
Today, 03:58
Joined
Feb 19, 2002
Messages
42,981
Please post the code that isn't working.
 

gino

Registered User.
Local time
Today, 07:58
Joined
Mar 16, 2000
Messages
117
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

Super Moderator
Staff member
Local time
Today, 03:58
Joined
Feb 19, 2002
Messages
42,981
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

New member
Local time
Today, 07:58
Joined
Jul 21, 2000
Messages
5
Hi Pat,
Thanks for the information. You just solved a problem I've been wrestling with since yesteday!
 

kkulick

Registered User.
Local time
Today, 07:58
Joined
Jul 11, 2000
Messages
19
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

Super Moderator
Staff member
Local time
Today, 03:58
Joined
Feb 19, 2002
Messages
42,981
You could do the same calculation in an unbound control. Setting a bound control to a calculation takes a little more work.
 

Mantispony

Registered User.
Local time
Today, 08:58
Joined
Mar 31, 2011
Messages
15
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

AWF VIP
Local time
Today, 07:58
Joined
Jan 22, 2010
Messages
26,374
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

Retired
Local time
Today, 07:58
Joined
Jun 2, 2003
Messages
12,701
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

Registered User.
Local time
Today, 08:58
Joined
Mar 31, 2011
Messages
15
Re: how do i replace "#NUM!" AND "#DIV/0!" in my reports?

The problem has been solved, thanks anyway :)
 

Users who are viewing this thread

Top Bottom