I have 10 questions in which 1,2,3,4,5,N/A can be the results. How do I calculate on this field to find Min, Max, Ave, etc...?


Thanks.

Since the field is not numeric (N/A is allowed), you have a bit of a challenge...For the non NA values you can use VAL(FieldName).

Have you considered using 0 instead of N/A? This way, you can have a numeric field and then you can calculate on it as needed.

HTH

You should definatly change the field to a numeric, it will make your life a whole lot easier with the database.

If I change it to Numeric, then "N/A" will not be allowed. I need the "N/A" option for questions that are not applicable. I don't want them to simply leave the question blank to ensure that all questions are answered before they continue.

Using "0" would skew averages, etc.

Using the Val(fieldname) is what I will have to do when I calculate, although it could get a little tricky. Any other suggestions?

You will have to calculate these aggregate functions using an expression such as the following:

Max(Iif(IsNumeric(YourField), val(YourField), Null))

This will treat the string value "N/A" (and any other non-numeric values) as nulls, which are ignored in the calculation of an SQL aggregate function such as max, min, or Avg.

If you simply used Val(), it would convert the "N/A"s to 0, skewing your averages, etc.

hope this helps
-AJ