Computing number along with a null value

[evergrean100]

I have three fields in a table set up in Access 2003 where it each has a choice of a rating number (1-4) along with an N/A option:
1 2 3 4 N/A

I then use the avg funciton to get and get the average of all three field values:

select avg(FieldOne + FieldTwo + Field3)
from tableOne;

It works great but the only problem is if N/A is selected the sql doesnt like the emply value (if selected, the N/A gets assigned a "" in my front end for the database) because it wants all numbers and nothing else to figure out the average. I could use 0 for the N/A value but was wondering how other people have handled this issue?

 

[PHV]

Perhaps this ?
SELECT Avg(Val(FieldOne) + Val(FieldTwo) + Val(Field3))
FROM tableOne;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886