Having Access calculate non IsNull responses.

[Goddoctor]

I have a report that is a series of text from textboxes and their labels;

Example:

Ears, Nose and Throat Examination:
Ears: Canals patent. Eardrums intact.
Nose: Septum midline. Mucosa normal.
Throat: Tonsils inflamed. Drainage present.
Skin Examination:
Hair: Normal hair distribution.
Neck: Normal skin.
Trunk: Normal skin.
Extremities: Rash consistent with poison ivy.

This is set up as:

Label1: Textbox1 (hidden)
Label2: Textbox2
Label3: Textbox3
Label4: Textbox4
Label5: Textbox5 (hidden)
Label6: Textbox6
Label7: Textbox7
Label8: Textbox8
Label9: Textbox9

Without changing the visible information in the report, I need Access to assign a value of 1 to those textboxes that contain data and a value of 0 to those that do not contain data. I then need to be able to sum, say, all of the positive responses in the "Ears, Nose, and Throat section", all of the positive responses in the "Skin section", and then sum those together.

Take into account I have a number of these "sections" and depending on the category and number of positive responses, I also need to assign a value of 1, 2, 3, 4 or 5 to be the final value. Example: If I have say six total positive responses overall, the final value needs to be 3. Also, if I have 4 positive responses in one "section", the final value may also need to be 3.

My question is, how do I accomplish this and have the final number (in the case of the immediately above examples, the number 3) show in a textbox at the bottom of the report.

Any help you could give would be greatly appreciated. Please use the above scenario to provide examples because I'm dense as a post on this one. Thanks.

Doc.

 

[Carol]

There are various ways to accomplish this, but the easiest way for you to see how this is performed is by the following:

In your underlying query for your report, create additional fields to handle the value portion of 1 for entry and 0 for null following the example below:

Rating:IIf(IsNull([FieldName]),"0","1")

Replace FieldName with the name of the actual field. Change "Rating" to another FieldName which would be unique and would become the Field Name called within your report for the results.

Next, place a text box in the footer section of each section, asking for a count of each new field making your record source as follows:

=Count [NewFieldName]

depending on how many sections and calculations that you want, you would probably want these count fields to be hidden.

Next create another text box within your footer section, creating a sum of the count fields on the formula that you want.

Next create another text box within your footer section to hold the value, as follows:

IIF([FieldName]=6, "3")

I hope that you can follow this through, and if you can't, report back.