if calculated field is null

[HEMASUR]

I have two fields that are calculated fields on my form
Eg:Field4 = (field1+field2)/field3)
Fields', 1, 2, 3 have a criteria set that if there is no data to show "NA".
The calculation works fine when there is data, but when there is a "NA" in any one of the fields, I get an error "Num#".
Can some one help me so that Filed4, displays "NA", instead of "Num#".
I tried my best with if statements but that did not work.
I could use all the help I can get!!
Thanks

 

[jimlee]

have you looked at the nz function?

jimlad

 

[jimlee]

have you looked at the nz function? seem to remember this was helpful to me once in a similar situation.

jimlad

 

[HEMASUR]

Yes I did, the nz function does not seem to work, if either of the fields, 1, 2, 3 has NA in them.

 

[jimlee]

I think I understand now, I used this which seemed to do the job....

=IIf((([Text1]="n/a") Or ([text2]="n/a") Or ([Text3]="n/a")),"there is a string here",[Text1]*[Text2])

jimlad

 

[jimlee]

sorry, should have substituted my names for yours and sorted out the calculation!

=IIf((([field1]="n/a") Or ([field2]="n/a") Or ([field3]="n/a")),"there is a string here",([field1]/[field2])+[field3])

jimlad

 

[HEMASUR]

Thanks Jimlee,
When I tried your suggestion, now I get an error "#Name?", I am thinking of creating another query based on the data, where all calculations will take place and base the form on that data.