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.

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!

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.