Receiving negative #s in Report

[Dorothy]

I have a rpt that has 15 fields (q1 thru q15) each
containing a value between 0 and 5. I'd like the report
to total the count of each field where the value is equal
to 4 or 5. This is what I've tried =Sum([q1]=4 Or [q1]=5)
When I run the rpt I receive the correct number but as a
negative "-15". Can anyone tell me what I'm doing wrong?
Please email through Newsgroup only. Thanks

 

[Fredg]

Dorothy,
You're not really doing anything wrong. You just haven't gone far enough
with your equation.
Look at it this way:
=Sum([q1]=4 Or [q1]=5)
will sum the result of the equation.

In words, what you have written is like this:
Does [q1]=4 Or Does [q1] = 5
If so the value of the expression is True.
Sum all the values.

Since True, in Access, is represented as -1 and False is 0, you had 15 True
results of your expression. So add -1 15 times and the Sum is -15.

Since any other value of [q1] results in a False (0) value, only True values
affect the Sum.

Wrap the equation in the Abs() function to return a positive answer.
=ABS(Sum([q1]=4 Or [q1]=5))

Alternatively, you could write:
=Sum(IIf([Q1]=4 OR [Q1]=5,1,0)
perhaps this expression would be easier to understand.

 

[Fredg]

Dorothy,
Sometimes Access Help can be a useful resource.
Look up the IIf() function (Immediate If).

Type IIf in any code window (or the Debug window).
Place the cursor within the word and then press F1.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Thanks Fred, that worked. I used the 2nd option. The only
question I have is what does the ,1,0 represent?

-----Original Message-----
Dorothy,
You're not really doing anything wrong. You just haven't gone far enough
with your equation.
Look at it this way:
=Sum([q1]=4 Or [q1]=5)
will sum the result of the equation.

In words, what you have written is like this:
Does [q1]=4 Or Does [q1] = 5
If so the value of the expression is True.
Sum all the values.

Since True, in Access, is represented as -1 and False is 0, you had 15 True
results of your expression. So add -1 15 times and the Sum is -15.

Since any other value of [q1] results in a False (0) value, only True values
affect the Sum.

Wrap the equation in the Abs() function to return a positive answer.
=ABS(Sum([q1]=4 Or [q1]=5))

Alternatively, you could write:
=Sum(IIf([Q1]=4 OR [Q1]=5,1,0)
perhaps this expression would be easier to understand.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

I have a rpt that has 15 fields (q1 thru q15) each
containing a value between 0 and 5. I'd like the report
to total the count of each field where the value is equal
to 4 or 5. This is what I've tried =Sum([q1]=4 Or [q1] =5)
When I run the rpt I receive the correct number but as a
negative "-15". Can anyone tell me what I'm doing wrong?
Please email through Newsgroup only. Thanks

.