data type number, or not???

[zencalc]

I'm creating a survey web page and creating a MS frontpage form to go back into a access db. no problem there, I've done it quite a few times... BUT, here's my dilemna. For the questions, I'd like to have the user choose a value from 1 to 10, or "NA" or <no value> if the question is not applicable. What data type should I use for the value fields? I'd like to think number, but the field's not going to like that pesky "NA". I want to be able to do some statistical analysis on the field, sum, average, mean etc. and can't think of another way to a)exclude the non-numeric value or b)give a value to the "NA" so it doesn't get included in my calculations. Any help would be appreciated, thanks!
Brian

 

[cyberbiker]

Let them select from a combo box with values N/A and 1 - 10
limit them to the list.

make the database fields numeric (Integer or Long, Integer uses less storage in the DB, Long should process a whisker faster on a 32 bit system so take your pick. Doubt anyone would notice the differences) and assign 0 as the default value

I would not use bound controls, but instead assign the text value like this:
If RS!field = 0 then
combo.text = N/A
else
combo.text = RS!field
end if

and vice versa
If combo.text = N/A then
RS!field = 0
else
Rs!Field = cint(combo.text) (Or Clng if you used a long)
end if


Terry (cyberbiker)

 

[Golom]

You could go either way but my instinct is to use numbers since you will be doing arithemetic on them. Rather than displaying the contents of the table directly, use a query that takes care of translating a numeric zero to "NA" for display purposes. Similarly, use an UPDATE query to set a value of zero when the user enters "NA" in the interface.

 

[zencalc]

thanks to both of you for the input. I had set up the fields as a dropdown and include the values 1-10 and default NA. So, I think I'll drop to import the data then use a query to transform any "NA"'s to <NULL>, and then run my analysis off that massged data... think would that work?
Brian