conditional countif or query on form

[fowlerryan]

Hi all,



I've been searching high and low on the web for this one so hope someone might have some suggestions and can bring my long quest to an end.

I'm working on a form which has 21 questions for the user to fill out. The potential outcomes for these questions for the user to select are done as a combo box for each one, with the following categories to chose from:

pass
ok
smallfail
bigfail
N/A

At the top of my form there is a field for the overall score which I am hoping to have automated so the user does not have to work it out of fill it in. The form was previously in excel and used a multiple countifs statements in a little table. If all 21 answers are pass or N/A, then the total score is pass. If any of the other options are selected at any given point, then the total score would be that one (in ranked order - big fail would be the overall score, even if small fail or ok was an answer to one of the 21 questions).

I understand access doesn't have a countif although you can combine the sum and Iif functions but i can only get it to pick up 1 of the fields and am trying to avoid having a whole matrix of hidden boxes counting up options. Is there some way that this could be set as a function in vba or in a query that i can rigger on the onchange? I'm trying to get it to score real time and not have to press a button to score the form at the end of filling in the form to work it out.

If you've got any ideas or need any more info, just let me know. Its my first db and i'd be really grateful for any help.

[Micron]

For me, not enough info on what you've got going on beyond the little information posted. As for real time, the combo box AfterUpdate event can provide that. However, even the CountIF function had to have something to count if, so what do you have if not hidden check boxes? Perhaps a hidden column of the combo that has a value you can count or sum, which is associated with the selection? I think you'll have to be more explicit about what makes up the design, or post a zipped copy of your db. I cannot help with db versions if they contain features that cannot be opened by 2007.