Replacing #Error Message 1

[Comp2008]

I’m building a Database which the data is constantly changing. Some fields have 0 (zero) data until the transaction is finished. Now, when a calculation is a division in the Query, for instance [Sold] divided by [Appointment Kept] I get a #Error message. We all know that the quotient of 0/0 is an error. So, if there are no Appointments, yet, obviously there are no Sells, and the result in Access is a #Error message. My question is, if there is a way to replace the #Error massage with a number zero until the data is updated (or the product is sold). I have two reasons why I’m trying to do this.
1. I don’t want to display a ”#Error” massage in the Query nor the Report. For esthetic purpose.
2. I get an “Overflow” window massage, if I Sum or Average a field with a “#Error” message.
Any Help will be greatly appreciated.

Thank you in advance.

Comp2008

 

[dhookom]

You can use the IIf() function to test the denominator like:

=IIf([Appointment Kept]=0,0,[Sold]/[Appointment Kept])

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Thank you dhookom. You gave me a good start point. Based on the expression you gave me I was able to solve the problem.

Comp2008