Changing negative number to a positive number

I have a report that pulls information from a query. The report is supposed
to calculate the number of people that responded 'Excellent', 'Good', 'Fair'
or 'Poor' to a survey and come back with a positive number. It keeps giving
a negative number.

I've tried subtracting 1 from the result (=[SumOfQ1excellent]*-1) and I've
tried using Abs (=Abs([SumOfQ1excellent])) but nothing is working.

Thank you,
Anita

On Fri, 9 Mar 2007 07:10:00 -0800, Anita
wrote:

I have a report that pulls information from a query. The report is supposed
to calculate the number of people that responded 'Excellent', 'Good', 'Fair'
or 'Poor' to a survey and come back with a positive number. It keeps giving
a negative number.

I've tried subtracting 1 from the result (=[SumOfQ1excellent]*-1) and I've
tried using Abs (=Abs([SumOfQ1excellent])) but nothing is working.

Thank you,
Anita

True() is stored as -1, False as 0 - so a Sum of Yes/No fields will be the
negative of the number of trues.

The Abs() function should work. In the initial query, instead of calculating
SumOfQ1Excellent as

Sum([Q1Excellent])

or whatever the expression might be, use

Sum(Abs([Q1Excellent]))

John W. Vinson [MVP]

I got a message that said "Extra ) in query expression
'Sum(Abs([Q1Excellent]))'."
--
Anita


"John W. Vinson" wrote:

On Fri, 9 Mar 2007 07:10:00 -0800, Anita
wrote:

I have a report that pulls information from a query. The report is supposed
to calculate the number of people that responded 'Excellent', 'Good', 'Fair'
or 'Poor' to a survey and come back with a positive number. It keeps giving
a negative number.

I've tried subtracting 1 from the result (=[SumOfQ1excellent]*-1) and I've
tried using Abs (=Abs([SumOfQ1excellent])) but nothing is working.

Thank you,
Anita

True() is stored as -1, False as 0 - so a Sum of Yes/No fields will be the
negative of the number of trues.

The Abs() function should work. In the initial query, instead of calculating
SumOfQ1Excellent as

Sum([Q1Excellent])

or whatever the expression might be, use

Sum(Abs([Q1Excellent]))

John W. Vinson [MVP]

Should I change my record source from my query to my table?

Anita


"Anita" wrote:

I got a message that said "Extra ) in query expression
'Sum(Abs([Q1Excellent]))'."
--
Anita


"John W. Vinson" wrote:

On Fri, 9 Mar 2007 07:10:00 -0800, Anita
wrote:

I have a report that pulls information from a query. The report is supposed
to calculate the number of people that responded 'Excellent', 'Good', 'Fair'
or 'Poor' to a survey and come back with a positive number. It keeps giving
a negative number.

I've tried subtracting 1 from the result (=[SumOfQ1excellent]*-1) and I've
tried using Abs (=Abs([SumOfQ1excellent])) but nothing is working.

Thank you,
Anita

True() is stored as -1, False as 0 - so a Sum of Yes/No fields will be the
negative of the number of trues.

The Abs() function should work. In the initial query, instead of calculating
SumOfQ1Excellent as

Sum([Q1Excellent])

or whatever the expression might be, use

Sum(Abs([Q1Excellent]))

John W. Vinson [MVP]

If it helps, the control source for my checkbox is 'Q1excellent'.
--
Anita


"Anita" wrote:

Should I change my record source from my query to my table?

Anita


"Anita" wrote:

I got a message that said "Extra ) in query expression
'Sum(Abs([Q1Excellent]))'."
--
Anita


"John W. Vinson" wrote:

On Fri, 9 Mar 2007 07:10:00 -0800, Anita
wrote:

I have a report that pulls information from a query. The report is supposed
to calculate the number of people that responded 'Excellent', 'Good', 'Fair'
or 'Poor' to a survey and come back with a positive number. It keeps giving
a negative number.

I've tried subtracting 1 from the result (=[SumOfQ1excellent]*-1) and I've
tried using Abs (=Abs([SumOfQ1excellent])) but nothing is working.

Thank you,
Anita

True() is stored as -1, False as 0 - so a Sum of Yes/No fields will be the
negative of the number of trues.

The Abs() function should work. In the initial query, instead of calculating
SumOfQ1Excellent as

Sum([Q1Excellent])

or whatever the expression might be, use

Sum(Abs([Q1Excellent]))

John W. Vinson [MVP]

On Mon, 12 Mar 2007 05:31:21 -0700, Anita
wrote:

I got a message that said "Extra ) in query expression
'Sum(Abs([Q1Excellent]))'."
--

Please copy and paste the entire SQL string to a message. The parentheses in
what you posted match - two left, two right; I suspect you have another (
somewhere in the query.

John W. Vinson [MVP]

Here it is:

SELECT tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class,
Count(tblRetirementRespondentold.RespondentNumber) AS
CountOfRespondentNumber, Sum(tblRetirementRespondentold.Q1excellent) AS
SumOfQ1excellent, Sum(tblRetirementRespondentold.Q2excellent) AS
SumOfQ2excellent, Sum(tblRetirementRespondentold.Q3excellent) AS
SumOfQ3excellent, Sum(tblRetirementRespondentold.Q4excellent) AS
SumOfQ4excellent, Sum(tblRetirementRespondentold.Q1good) AS SumOfQ1good,
Sum(tblRetirementRespondentold.Q2good) AS SumOfQ2good,
Sum(tblRetirementRespondentold.Q3good) AS SumOfQ3good,
Sum(tblRetirementRespondentold.Q4good) AS SumOfQ4good,
Sum(tblRetirementRespondentold.Q1fair) AS SumOfQ1fair,
Sum(tblRetirementRespondentold.Q2fair) AS SumOfQ2fair,
Sum(tblRetirementRespondentold.Q3fair) AS SumOfQ3fair,
Sum(tblRetirementRespondentold.Q4fair) AS SumOfQ4fair,
Sum(tblRetirementRespondentold.Q1poor) AS SumOfQ1poor,
Sum(tblRetirementRespondentold.Q2poor) AS SumOfQ2poor,
Sum(tblRetirementRespondentold.Q3poor) AS SumOfQ3poor,
Sum(tblRetirementRespondentold.Q4poor) AS SumOfQ4poor
FROM tblcourse INNER JOIN tblRetirementRespondentold ON (tblcourse.trainer =
tblRetirementRespondentold.trainer) AND (tblcourse.coursenumber =
tblRetirementRespondentold.coursenumber) AND (tblcourse.coursedate =
tblRetirementRespondentold.coursedate)
GROUP BY tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class
HAVING (((tblcourse.trainer) Like [Trainer Name]) AND
((tblRetirementRespondentold.coursedate) Between [Beginning Date] And [Ending
Date]) AND ((tblRetirementRespondentold.coursenumber)=[Forms]![ENTER COURSE
NUMBER]![coursenumber]));

--
Anita


"John W. Vinson" wrote:

On Mon, 12 Mar 2007 05:31:21 -0700, Anita
wrote:

I got a message that said "Extra ) in query expression
'Sum(Abs([Q1Excellent]))'."
--

Please copy and paste the entire SQL string to a message. The parentheses in
what you posted match - two left, two right; I suspect you have another (
somewhere in the query.

John W. Vinson [MVP]

On Mon, 19 Mar 2007 05:27:05 -0700, Anita
wrote:

Here it is:

Ok, let's parse this out. I'll be SO glad when Microsoft allows comments and
retains layout in the SQL window:

SELECT tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class,
Count(tblRetirementRespondentold.RespondentNumber) AS CountOfRespondentNumber,
Sum(tblRetirementRespondentold.Q1excellent) AS SumOfQ1excellent,
Sum(tblRetirementRespondentold.Q2excellent) AS SumOfQ2excellent,
Sum(tblRetirementRespondentold.Q3excellent) AS SumOfQ3excellent,
Sum(tblRetirementRespondentold.Q4excellent) AS SumOfQ4excellent,
Sum(tblRetirementRespondentold.Q1good) AS SumOfQ1good,
Sum(tblRetirementRespondentold.Q2good) AS SumOfQ2good,
Sum(tblRetirementRespondentold.Q3good) AS SumOfQ3good,
Sum(tblRetirementRespondentold.Q4good) AS SumOfQ4good,
Sum(tblRetirementRespondentold.Q1fair) AS SumOfQ1fair,
Sum(tblRetirementRespondentold.Q2fair) AS SumOfQ2fair,
Sum(tblRetirementRespondentold.Q3fair) AS SumOfQ3fair,
Sum(tblRetirementRespondentold.Q4fair) AS SumOfQ4fair,
Sum(tblRetirementRespondentold.Q1poor) AS SumOfQ1poor,
Sum(tblRetirementRespondentold.Q2poor) AS SumOfQ2poor,
Sum(tblRetirementRespondentold.Q3poor) AS SumOfQ3poor,
Sum(tblRetirementRespondentold.Q4poor) AS SumOfQ4poor
FROM tblcourse
INNER JOIN tblRetirementRespondentold
ON (tblcourse.trainer = tblRetirementRespondentold.trainer)
AND (tblcourse.coursenumber = tblRetirementRespondentold.coursenumber)
AND (tblcourse.coursedate = tblRetirementRespondentold.coursedate)
GROUP BY tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class
HAVING (
((tblcourse.trainer) Like [Trainer Name])
AND
((tblRetirementRespondentold.coursedate)
Between [Beginning Date] And [Ending Date])
AND
((tblRetirementRespondentold.coursenumber)=
[Forms]![ENTER COURSE NUMBER]![coursenumber]));

I don't see any parenthesis errors: are you still getting them?

I'd suggest changing the word HAVING to WHERE - HAVING sums all the records in
the entire table and then discards those sums which don't fit the criteria,
but WHERE filters the records first and then sums, so it's much more
efficient. Try copying and pasting this entire SQL string into the SQL window
of a new query; change HAVING to WHERE; and see if that works for you.

John W. Vinson [MVP]

I'm not getting the parenthesis errors, but that's because the report won't
let me go from Design View to Print Preview using Sum(Abs([Q1excellent])).

When I try to replace HAVING to WHERE I get the error below:

Syntax error (missing operator) in query expression tblcourse.class WHERE
(((tblcourse.trainer) Like [Trainer Name]) AND
((tblRetirementRespondentold.coursedate) Between [Beginning Date] And [Ending
Date]) AND ((tblRetirementRespondentold.coursenumber)=[Forms]![ENTER COURSE
NUMBER]![coursenumber]));

I've tried recreating the query and the table, but nothing seems to work.
Is this hopeless?
Anita


"John W. Vinson" wrote:

On Mon, 19 Mar 2007 05:27:05 -0700, Anita
wrote:

Here it is:

Ok, let's parse this out. I'll be SO glad when Microsoft allows comments and
retains layout in the SQL window:

SELECT tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class,
Count(tblRetirementRespondentold.RespondentNumber) AS CountOfRespondentNumber,
Sum(tblRetirementRespondentold.Q1excellent) AS SumOfQ1excellent,
Sum(tblRetirementRespondentold.Q2excellent) AS SumOfQ2excellent,
Sum(tblRetirementRespondentold.Q3excellent) AS SumOfQ3excellent,
Sum(tblRetirementRespondentold.Q4excellent) AS SumOfQ4excellent,
Sum(tblRetirementRespondentold.Q1good) AS SumOfQ1good,
Sum(tblRetirementRespondentold.Q2good) AS SumOfQ2good,
Sum(tblRetirementRespondentold.Q3good) AS SumOfQ3good,
Sum(tblRetirementRespondentold.Q4good) AS SumOfQ4good,
Sum(tblRetirementRespondentold.Q1fair) AS SumOfQ1fair,
Sum(tblRetirementRespondentold.Q2fair) AS SumOfQ2fair,
Sum(tblRetirementRespondentold.Q3fair) AS SumOfQ3fair,
Sum(tblRetirementRespondentold.Q4fair) AS SumOfQ4fair,
Sum(tblRetirementRespondentold.Q1poor) AS SumOfQ1poor,
Sum(tblRetirementRespondentold.Q2poor) AS SumOfQ2poor,
Sum(tblRetirementRespondentold.Q3poor) AS SumOfQ3poor,
Sum(tblRetirementRespondentold.Q4poor) AS SumOfQ4poor
FROM tblcourse
INNER JOIN tblRetirementRespondentold
ON (tblcourse.trainer = tblRetirementRespondentold.trainer)
AND (tblcourse.coursenumber = tblRetirementRespondentold.coursenumber)
AND (tblcourse.coursedate = tblRetirementRespondentold.coursedate)
GROUP BY tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class
HAVING (
((tblcourse.trainer) Like [Trainer Name])
AND
((tblRetirementRespondentold.coursedate)
Between [Beginning Date] And [Ending Date])
AND
((tblRetirementRespondentold.coursenumber)=
[Forms]![ENTER COURSE NUMBER]![coursenumber]));

I don't see any parenthesis errors: are you still getting them?

I'd suggest changing the word HAVING to WHERE - HAVING sums all the records in
the entire table and then discards those sums which don't fit the criteria,
but WHERE filters the records first and then sums, so it's much more
efficient. Try copying and pasting this entire SQL string into the SQL window
of a new query; change HAVING to WHERE; and see if that works for you.

John W. Vinson [MVP]

On Tue, 20 Mar 2007 06:51:18 -0700, Anita
wrote:

When I try to replace HAVING to WHERE I get the error below:

Syntax error (missing operator) in query expression tblcourse.class WHERE
(((tblcourse.trainer) Like [Trainer Name]) AND
((tblRetirementRespondentold.coursedate) Between [Beginning Date] And [Ending
Date]) AND ((tblRetirementRespondentold.coursenumber)=[Forms]![ENTER COURSE
NUMBER]![coursenumber]));

Nah, far from hopeless - just needs some more tweaking!

One change you need is to get away from the LIKE operator. It uses wildcards,
but here you don't WANT wildcards - you want to exactly match the trainer
name, right? What is the datatype of tblCourse.Trainer though? Is it a
shudder Lookup Field? And why are you prompting for the trainer name and
beginning and end date with popup prompts, while getting the course number
from a form? What are the datatype of Coursedate and Coursenumber while we're
at it?

For now try:

WHERE
tblcourse.trainer = [Trainer Name]
AND
tblRetirementRespondentold.coursedate = CDate([Beginning Date])
AND
tblRetirementRespondentold.coursedate DateAdd("d", 1, [Ending Date])
AND
tblRetirementRespondentold.coursenumber=
[Forms]![ENTER COURSE NUMBER]![coursenumber];



John W. Vinson [MVP]