problem with query totals for check boxes

[worldcrafter]

I have the following "Yes/No" fields in a table:

applying 1
applying 2
applying 3
applying 4

In a query I am trying to get the total number of times a check box was checked for these fields. In query design view, I clicked on the Sigma sign and set the total row for the above fields to "Sum". The problem is that the sums for the fields are correct numerically, but they have a negative number in front of them.

For example, if the "applying 1" field was checked 5 times out of all the records in my table, then my query will show the total "-5" for the sum when I run the query.

what is causing the query to give a negative number, and how can I fix it so it becomes positive?

 

[pbaldy]

It's negative because the value stored for Yes/True is -1. You could surround your formula with the Abs() function to force it to positive.

 

[paulreed]

I am not sure where the Abs() function should be added. Can it be added to the query itself, as I have tried to add it in SQL view without luck. The Access help file does not detail this function.

 

[pbaldy]

Yes, it can be in a query. Simply surround any existing calculation like:

Abs(Field1 + Field2 - Field3)

If you can post what you tried that failed, we can probably spot the problem.

 

[paulreed]

Pbaldy

Thanks for your help.
I have attached a file describing the problem, and as can be seen from the query, I have been unable to change the sum from negative to positive.
Eventually, I want to be able to calculate the percentage submitted each day, i.e. 20% submitted, 80% failed, but will probable acheive that through a report calculation.
As you will probably appreciate, I am analysing an existing database, therefore I cannot change the table itself.
Thanks

Paul

 

[pbaldy]

This returns the same numbers as the existing query, but all positive:

Number of Returns: Abs([Returns])

 

[paulreed]

Pbaldy

That works a treat! Thanks.

Paul