Option Buttons convert values to text

[TimMoffy]

I am designing a database for our audit teams to carry out reviews of our claims handlers and have put next to each of the questions on a form an option group of three option buttons, "Yes", "No", "Not Applicable" with the values "1", "-1" and "0" respectively. This value is then stored in a table under the relevant field, intQuestionValue, for Q1, Q2, Q3 etc.

This works well for calculating scores, percentages etc. for all the questions. However the manager of the team has said that there will be times when he wants to report by the number of "No" answers for example, so that he look at trends and build these into training plans etc.



I do have a table, tblScores, which has two columns in it. One for the text element, "Yes", "No", "N/A" and the other has the corresponding numeric value in it.
Because there are numerous questions i cannot seem to create a join in a query for the numeric value to bring back the text value. Any ideas how i could do this?
The only other method that came to mind was to write the text outcome to a table as well at the same time, but I'm not sure how I would do this at the same time as clicking the option button, or would it all save when i (the user) closes the form?

Thanks in advance for reading and any help

[JeroenMioch]

Ive read your post a few times and i hope im understanding it correctly.
Your manager wants to know howmany people have answered "no" in some question (or all ?)
Maybe there is a better way but you can easily do this by adding a SQL statement in the onlick event of the "no" option of your option button. But by doing so, the value is directly written once you click it, but with most forms a user can select an option and then click a "go" or "next page" button wich would make the code a little more complicated. But not all too much

 
dim strSQL as string
strSQL = "Update tblScores set [score] = [score] +1"
docmd.runSQL strSQL

Assuming the field in your tblScores is called scores. You have to set the standard value of that field in your table to "0" for it to work. This is because your updating a value, so that value has to be there first (hence the 0)

Now if you want to write a value to the table in combination with a "next" button of some sort :

If me.YourRadio_NO_Button.value = true then
dim strSQL as string
strSQL = "Update tblScores set [score] = [score] +1"
docmd.runSQL strSQL
else
'nothing
end if

I hope this helps, if not post back with more info

[TimMoffy]

Thanks for getting back to me. Think i need ot read this a couple of times and try it over the weekend. I'll let you know on Monday how it went.
Thanks again

[TimMoffy]

JeroenMioch


Thanks for your help. I read this and retried it several times and whilst I didnt use this solution, it did lead me to a solution, using the tbl that stroed both the numerical values and the text values, in a query. Seems so simple once you have solved the problem, but at the time it was drinving me mad. Many thanks for your help and pointing me in the right direction.