Divide by Zero on Report (#Num!)

[ollyb303]

Hi,

I have created a report in my Access db which has two columns - I'll call them Number1 and Number2. I have added a textbox to calculate a percentage from these two numbers

=([Number1]/[Number2]) with the format property set as percentage.

My problem. Sometimes the number in Number2 is a zero, which means the result of the expression is invalid and gives me "#Num!" - I understand why this is happening, but I wondered if there was a way around it.

What I would like to happen is that when it is trying to divide by zero, the #Num! is substituted for a blank space or a zero.

This is purely cosmetic as I would like my report to look neater.

Any help gratefully received.

Thanks,

Olly

[NeoPa]

Try setting the result to :

=IIf([Number2]=0,[Number1],[Number1]/[Number2])

[ollyb303]

Perfect! Thanks so much, this works a treat.

[NeoPa]

No worries Olly. Just pleased it helped :)

[missinglinq]

Or, for those who find iif() confusing, and it can be, especially if nested 12 times, as some are wont to do:

= [Number1] / Nz([Number2], 1)

Linq ;0)>

[NeoPa]

Good point Linq (in fact that's how I would tend do it myself). I wasn't sure without checking if Nz() was available as a formula within a control though.

[missinglinq]

Actually, Ade, if you think about it, if one Access function would work in a Control Source, the other would, but in point of fact both of our codes were lacking! Mine took care of the problem if Number2 was Null, and yours took care of the problem if Number2 = 0, but neither took care of both possibilities! So I modified yours!

=IIf([Number2]= 0 or IsNull([Number2]),[Number1],[Number1]/[Number2])

Linq ;0)>

[NeoPa]

Actually, Ade, if you think about it, if one Access function would work in a Control Source, the other would,

Actually, Nz() is "special" in this respect. I can't remember the finer details of exactly where I've found this (may even be in Excel rather than Access) but there certainly is somewhere where Nz() is not available but other functions are. Sorry I can't be more specific at this time.

but in point of fact both of our codes were lacking! Mine took care of the problem if Number2 was Null, and yours took care of the problem if Number2 = 0, but neither took care of both possibilities! So I modified yours!

=IIf([Number2]= 0 or IsNull([Number2]),[Number1],[Number1]/[Number2])

Linq ;0)>

This comes back to the original question (which I overlooked in my last response but was better focused on for my first) which was that a 0 can sometimes cause a problem. I think it only needs to handle that situation. It doesn't hurt to have the fuller formula there in case others find the thread though and DO have that requirement too.

[missinglinq]

Well, of course, you'ved piqued my curiosity now! Turns out that VBScript doesn't support the NZ() function, nor can you use it with SQL Server or Access Projects!

It's like Billy Bob Gate's boys said, "Okay, we've created this handy function, now let's see how many places we can find where we won't let it work!"

Linq ;0)>

[NeoPa]

Something like that ;)
I think I fell over it in an Excel formula though. I just tried it and it doesn't work in there (A1 = "=Nz(B1)" results in #NAME?). I'm not sure where else though.