report calculation

[newbie2181]

Hi i am try to perform and average calculation on a field in a table which is fine and i can do that without any problems. My problem arises due to the fact that the fields can contain the word 'refer' which you can obviously not perform a calculation on. Is there any way that i can perform the "=AVG([field1]) calculation but ignore all refers so i don't get the data type mismatch error?

Thanks

Dan

 

[dhookom]

You might need a couple

=Sum(IIf([Field1]=&quot;refer&quot;,0,[Field1]))/Sum(Abs([Field1]<>&quot;refer&quot)

You may have to add more IIf() to protect from divide by zero in the event there all records have &quot;refer&quot;.

Duane
MS Access MVP

 

[newbie2181]

nice one mate cheers i'll have a play about with that and see how it goes!


Thanks

Dan