Hi! I am having difficulty in coming up with a way to average groups of

values on a current record. I am using Access '97.

I have developed a database application that charts results from a

questionnaire. Each questionnaire has 112 questions to which there is an

answer (0,1,2,3,4 or X). The answers are entered into a form creating a

record. The table format as a textfile would look like this (with the

customer number at the beginning):

STONG01,1,0,X,2,4,4,3,2,4,1,1,0, etc...(113 fields)

JONES04,0,1,2,3,4,2,1,4,X,4,3,0, etc...

What I need to do is (using only the current record showing in the form)

to average the first 7 answer values (fields) and put that result either

in a variable or on another form. Then the next 7 answer values must be

averaged and the result put in a different variable. I would end up with

six{*filter*} unique variables with resulting averages in them. How do I do

this in a function considering I had to use text fields in the table

because some of the answer values are text ("X"). Also, the values need

to be taken from the current record in the table because the answer

values aren't on the form (The averaged results end up being used in an

embedded chart on the same form). As well, only values not "X" can be

averaged so I might average 6 or 5 values.

I would think the function would have to loop through each group of 7

answer values, using the variant datatype, count the valid answer values

within each group of 7 answers, sum the valid answers and divide by the

count. Or can the Access Avg function be used?

Am I on the right track? I have tried queries with limited success. The

Avg function in MS Excel worked fine because of the "general" data

format, but I can't use that as a datatype in a table.

Help would be appreciated! Thanx!