access combo box/report question

[velma_b]

I am using Access 97. I have a form with 8 combo boxes. Each box has "yes", "no", and "n/a" as selection choices. I need a report that will count only the "yes" answers in each column. For example:


Acct No. ComboA ComboB ComboC


22222 Yes No N/A

44444 No Yes Yes

55555 Yes No N/A



2 1 1



First Question: Is it possible to count only the “yes” answers with combo boxes or do I have to switch them to option groups (I don’t have room on my form for all those option boxes)? I found a reference in an old Access book about assigning values to transform the text to numbers in an option group, but nothing about combo boxes. I tried entering:



=IIf([ComboA]=”yes”,”1”,IIf([ComboA]=”no”,”2”,IIf([ComboA]=”n/a”,”3” )))



in the “criteria” box in the query, but when I run the report, I get an “enter parameter value” message and no results.

Second Question: I’m teaching myself Access as I go (and not doing a very good job so far) – can you recommend a good basic training manual I can purchase?

Thanks for any and all assistance.

Velma_b

 

[SydneyGeek]

You could try creating a calculated textbox for each Combo. They will need to be on the report (not the form) and you will use these to get your totals.
When you build the report, use the textboxes instead of the combos for the summary data.
Example: Use the Report Wizard to build the report. Set it out so that you have [Name], [Other Fields], [FieldA], [FieldB], .... [FieldH]. Pick the least terrible layout style. Choose Landscape for the layout to reduce crowding. You'll then have a row for each record.
To get the totals for each "yes", go to Design View and insert a Report Footer. (The Help files should show you how). Into this footer, insert a TextBox and align it below the FieldA text box in the Detail section. Set its control Source to be =Sum(Iif([FieldA]="Yes",1,0)). Save the report, preview it, and if it works, go back to design view. Copy that control, paste it below each of the other fields (adjacent to the first one), and change the Control Source to suit.

Lengthy explanation, but I hope it helps.
Denis