Receiving negative #s in Report

  • Thread starter Thread starter Dorothy
  • Start date Start date
D

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
 
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.
 
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.


Dorothy said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top