423,491 Members | 2,205 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,491 IT Pros & Developers. It's quick & easy.

Divide by Zero on Report (#Num!)

ollyb303
P: 74
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
Jan 4 '08 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,037
Try setting the result to :
Expand|Select|Wrap|Line Numbers
  1. =IIf([Number2]=0,[Number1],[Number1]/[Number2])
Jan 4 '08 #2

ollyb303
P: 74
Perfect! Thanks so much, this works a treat.
Jan 4 '08 #3

NeoPa
Expert Mod 15k+
P: 31,037
No worries Olly. Just pleased it helped :)
Jan 4 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
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)>
Jan 5 '08 #5

NeoPa
Expert Mod 15k+
P: 31,037
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.
Jan 5 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
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)>
Jan 5 '08 #7

NeoPa
Expert Mod 15k+
P: 31,037
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.
Jan 5 '08 #8

missinglinq
Expert 2.5K+
P: 3,532
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)>
Jan 6 '08 #9

NeoPa
Expert Mod 15k+
P: 31,037
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.
Jan 6 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.