I don't want to see #num! on my report can I mask it with '0' 1

[mearmortal]

I have a calculation which is (0/1234)*1000000 sometimes,
and it returns #num!
Is their a way of trapping this and substitute the returned value with '0'

Suggestions please

 

[mrf1xa]

Check out the IIF function- this should do what you want.

 

[mearmortal]

I have an IIf function but whats the sytax for filtering the #num!?

 

[mrf1xa]

Mmm, not as easy as I though then

OK, how about trying to capture the probelm earlier on? How about something like this in the AfterUpdate event of whatever control is causing the problem:

If IsNumeric (Me![NameofyourControl]) Then

Else
Me![NameofyourControl] = 0
End If

Seems to work on a quick test here....

 

[mearmortal]

I must say you suggestion about trying to trap things earlier paid off. I was not able to put the if then else onto the report but I changed the test argument I was using to look at one of the calculated fields from the query and test it for 0, after doing this it worked: -

=IIf(([SumofDefectQuantity])=0,0,([DPMOValue]/[SumofDPMOValue]))

This is the other calculated field: -

DPMOValue: ([SumofDefectQuantity]/([SumofKey]*[OFDNumber]))*1000000

Thanks for your help.

 

[oharab]

FYI: you can trap the error using iserror() so that (0/1234)*1000000 becomes
=iif(iserror((0/1234)*1000000),0,(0/1234)*1000000)

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------