#Num! - Going crazy

[TheHunter]

:banghead:
In my table, I have two fields that are sometimes going to be zero (both fields zero at the same time). When the field that contains a formula that includes one of the fields that contain the zero processes the operation, it returns the #Num!

For example,

Number of Hours Estimated
Number of Hours Incurred
=% over or under the estimated number of hours estimated.

Please help

 

[Galaxiom]

Divide by zero is going to be a problem.

You need to use some way to avoid it.
Here is one way.

=Iif([whatever]=0,Null,[something]/[whatever])

 

[TheHunter]

I know I'm being a baby about this, but I've been working on this all day and trying to resolve it myself.
The names of three fields are: Actual Hours Incurred, Hours Estimated, and % over/under.

Can you please give me the full expression that I need to use.

 

[Galaxiom]

The names of three fields are: Actual Hours Incurred, Hours Estimated, and % over/under.

=Iif([Hours Estimated]=0,Null,[Hours Incurred]/[Hours Estimated])

It goes into the ControlSource of the [% over/under] textbox on the form or report.

Special characters should be avoided as should spaces in names. Starting a name with one is even worse.

 

[TheHunter]

Oh my goodness. Now I get #Name? in the % over under field on all of the forms/records.
:banghead:

 

[Galaxiom]

That means it can't find one of the fields or control. I left out part of the name.

=Iif([[COLOR=blue]Acutal[/COLOR] Hours Estimated]=0,Null,[Hours Incurred]/[[COLOR=blue]Actual[/COLOR] Hours Estimated])

Be aware that the names of fields don't have to be what you want displayed in the labels on forms and reports. You can edit the label captions to anything you want.

This way you can keep the field names short and save youself a lot of typing when you get into coding. Avoiding spaces and special characters means you don't have to put the square brackets around everything too.

 

[TheHunter]

Last try hopefully:

=IIf([Motions - No of Hours Estimated:])=0,Null,[Motions - Actual Hours Incurred:]- [Motions - No of Hours Estimated:])/[Motions - No of Hours Estimated:]

The actual names of the three fields are:

Motions - No of Hours Estimated:
Motions - Actual Hours Incurred:
Motions % over/under

The last field (Motions % over/under) is what I'm trying to calculate. I REALLY appreciate your help and working with me on this.

 

[Galaxiom]

Your field/control names are even worse than originally stated.

However I really doubt you would have ended the names with a colon. I expect you are using the label caption which Access appends a colon to by default.

Use the Name Property of the object you are referring to.