Average of an option group

[StephenB]

I have a report that's based on a query whose form has an option group. The Options (and Values) are N/A (0), Yes (1) and No (2). I have the option group set to store the numeric value in the table. I need to report the average of the option group without including the N/A's.

I need three records with the results of "Yes", "No" and "N/A" to return an average of "1.5", not "1". Any suggestions? It's still early in the developement of the db, so I'm open to making changes on any object.

 

[StephenB]

Thank you, Pat. I had been working the issue the way you are suggesting, but everything I had tried failed. I'm not familiar with writing the code you mention above. Could you provide more information on the code itself and where/how I should use it? I'd be grateful.

Thanks in advance,
Stephen