Summing YES/NO in a report

[Volk359]

Greetings Folks,

I've searched around this forum and got some helpful answers but none of the coding I've got from here solves what I'm trying to do.

I'd like to create a single page report that sums all the yes/no answers (radio buttons) from a questionnaire. I've created a query from the main table using the original fields and an IIf to get a numerical value and the following code(s) in my report:

Query code (in the field box):

field_name_value: IIf([field_name]=YES,1,0)

This does return 1 if field_name is checked and 0 if not.

Report code (in a text box):

=Sum(IIf([query]!field="YES",1,0))
=Sum(IIf([query]!field="-1",1,0))
=Sum(IIf([query]!field_value="1",1,0))
=Sum(Abs([query]!field_value))

The only response I get from any of these codes in the report is #ERROR.

Am I missing something?

 

[scriverb]

First of all if you had set these fields up as Yes/No boolean fields(-1 or 0) then you could just use a ABS function to convert the values to 1 and 0 without the need for the IIF function.

I would perform all of these aggregate summing function in a query that would be the Record Source for my report. You can perform Group By and Sum function on the fields as you have tried in this report and then passs just the single record to the report for printing in the Detail Section.

This is the simpliest way to do this. Post back if you need help setting up the query.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Volk359]

Sounds like it may be easier to change all the Yes/No's to -1 and 0. If that's an easy way out I'll go for that.

If not then I'll need help setting up the query.

Thanks,

Keith

 

[scriverb]

They are two seperate things. But, i would do both. Change the field type and use a query to roll up the answers.

Get back with me if you need help.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Volk359]

The only field types available are yes/no, true/false and on/off. None of these give me -1 or 0. I guess I'm looking in the wrong area.

 

[scriverb]

In the design window of a table design each field has a different type. One is yes/No. This is a Boolean field meaning if it is stored as a Yes/No or True/False or On/Off and ultimately as a numeric value -1 or 0 respectively.

The field can be evaluated in an expression using any of these key words. But, evaluated to "Yes" or "No" as strings will not be successful. What have you got setup in your field? If it is a Yes/No field that that is okay and you were just making the comparison incorrectly.

You see if we use the function ABS(absolute) with a Yes/No field then a -1 is converted to 1 and 0 stays the same. So you can just add up the Sum(ABS([fieldname])) to find the number of Yes responses. The No responses can be calculated by the ([total responses] - Sum(ABS([fieldname]))).

Let me know where you stand and we can proceed.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Volk359]

Sounds like I was already on the right track. My table fields were all set up with data type yes/no and format yes/no. The report has the =sum(abs([fieldname])) formula but I still get the #error.

 

[scriverb]

Like I said the Sum function should be used in the query that feeds your report as its Record Source. This would be the best way to create this report. In your query we want to use the Total function to group all of the records into one. Then each of the fields that need to be Totaled would have the Sum Aggregate applied. If you would post your existing query or table info I could give you an example.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Volk359]

You're just like my Dad, always having to explain things twice to me. ;-)

I put the formula in the query and lo and behold it works. I can run the report wizard and it sets me right up.

Thanks for you help, Bob!