Averages 1

[Ptrif]

I have a report that where i average a group of totals using something similiar to this Avg([CSTOTAL]). This normally work fine for my purposes, however, some of the fields may contain a "N/A" instead of a 1, 2 or 3 value and so when trying to run the report I get an error about the expression being to complex, is there a way to get the average and just ignore the N/A in effort to NOT get this error? any help is appreciated.

Thanks
Paul

 

[CosmoKramer]

Paul,

Try something like this as the control source for the average text box:

=DAvg("Val([CSTOTAL])","YourQueryName","[CSTOTAL] <> 'N/A'")

The Val function will return a numeric value and the criteria will exclude the "N/A" records....

Hoc nomen meum verum non est.

 

[Ptrif]

I still recieve the same error message.....? Anything else i can try?

 

[dhookom]

Try:
=Sum(Abs([CSTOTAL]<>"N/A") * Val([CSTOTAL]))/ Sum(Abs([CSTOTAL]<>"N/A"))
This assumes there will always be at least one record where CSTOTAL is not "N/A". If this isn't true, you will need an IIf() to avoid divide by zero.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[CosmoKramer]

Did you try my suggestion in an unbound text box?? I've used similar statements before, and I tested this one before I posted.....

Hoc nomen meum verum non est.

 

[Ptrif]

Cosmo, i did use it in an unbound text box, I will review it once again and give it another try.

 

[Ptrif]

Dhookom, THANKS!!! that worked great!

I thank you both for your help!

Paul

 

[dhookom]

Cosmo's solution should work (although slow) however if the report is opened with a filter, it will fail since the report's records will not match [YourQueryName].

Duane
MS Access MVP
Find out how to get great answers faq219-2884.