Changing negative number to a positive number

G

Guest

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
 
J

John W. Vinson

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

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]
 
G

Guest

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


John W. Vinson said:
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
Click to expand...

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]
Click to expand...
 
G

Guest

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

Anita


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


John W. Vinson said:
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
Click to expand...

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]
Click to expand...
Click to expand...
 
G

Guest

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


Anita said:
Should I change my record source from my query to my table?

Anita


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


John W. Vinson said:
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]
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

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

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]
 
G

Guest

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 said:
I got a message that said "Extra ) in query expression
'Sum(Abs([Q1Excellent]))'."
--
Click to expand...

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]
Click to expand...
 
J

John W. Vinson

Here it is:
Click to expand...

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]
 
G

Guest

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 said:
Here it is:
Click to expand...

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]
Click to expand...
 
J

John W. Vinson

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]));
Click to expand...

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]
 
G

Guest

How can I tell what the datatype is for tblcourse.Trainer? Looking at my
table, the data type for Coursedate is date/time and for Coursenumber is text.

I may be prompting wrong, but I need to pull up the reports by trainer,
course code and date. I copied the prompts from a database someone else had
created.

I'm getting a syntax error message when I try the expression below. I'm
using it in place of
HAVING (((tblcourse.trainer) Like [Trainer Name]) AND
((tblRetirementRespondentold.coursedate) Between [Beginning Date] And [Ending
Date]) AND ((tblRetirementRespondentold.coursenumber)=[Forms]![ENTER COURSE
NUMBER]![coursenumber]));

Has anyone told you lately that you have the patience of a saint?
Especially since you're answering my questions! :-D
--
Thanks!
Anita


John W. Vinson said:
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]));
Click to expand...

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]
Click to expand...
 
G

Guest

How can I tell what the datatype is for tblcourse.trainer? The datatype for
coursedate is date/time and for coursenumber is text.

I'm probably prompting wrong, but I need to prompt for the trainer name,
date and course number so that the right report pulls up. I copied the
prompts from someone else's database (I know - 50 lashes with a wet noodle
for that one).

Has anyone told you lately that you have the patience of a saint?
Especially since you're answering my questions! :-D
--
Thanks!
Anita


Anita said:
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 said:
Here it is:
Click to expand...

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]
Click to expand...
Click to expand...
 
J

John W. Vinson

How can I tell what the datatype is for tblcourse.Trainer? Looking at my
table, the data type for Coursedate is date/time and for Coursenumber is text.
Click to expand...

Open tblCourse in design view. Select the Trainer field. Look in the datatype
column - is the datatype Text, or Number, or what? Also look on the Lookup tab
in the field properties - if the Display Control is "Combo Box" you've fallen
victim to the abominable Lookup Field misfeature. The table actually contains
a numeric ID but what you SEE is the trainer name; searching the field using a
name as a criterion won't work, since "Einstein, A." is not equal to 315.
I may be prompting wrong, but I need to pull up the reports by trainer,
course code and date. I copied the prompts from a database someone else had
created.
Click to expand...

Well... unless your table has the same fieldnames and fieldtypes, this may not
work. You've got to have at least some knowledge of what code is doing before
you blindly copy it!
I'm getting a syntax error message when I try the expression below. I'm
using it in place of
HAVING (((tblcourse.trainer) Like [Trainer Name]) AND
((tblRetirementRespondentold.coursedate) Between [Beginning Date] And [Ending
Date]) AND ((tblRetirementRespondentold.coursenumber)=[Forms]![ENTER COURSE
NUMBER]![coursenumber]));
Click to expand...

Please (again) open the query in full SQL view and post it here.
Has anyone told you lately that you have the patience of a saint?
Especially since you're answering my questions! :-D
Click to expand...

The accusation has been made before... but not by my family... <g>

John W. Vinson [MVP]
 
G

Guest

The datatype in tblcourse is text and the lookup field is text box.

Here is the code again. I used the expression you said to use earlier, but
it doesn't like when I change HAVING to WHERE.

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.coursedate = tblRetirementRespondentold.coursedate) AND
(tblcourse.coursenumber = tblRetirementRespondentold.coursenumber) AND
(tblcourse.trainer = tblRetirementRespondentold.trainer)
GROUP BY tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class
HAVING (((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]));
--
Anita


John W. Vinson said:
How can I tell what the datatype is for tblcourse.Trainer? Looking at my
table, the data type for Coursedate is date/time and for Coursenumber is text.
Click to expand...

Open tblCourse in design view. Select the Trainer field. Look in the datatype
column - is the datatype Text, or Number, or what? Also look on the Lookup tab
in the field properties - if the Display Control is "Combo Box" you've fallen
victim to the abominable Lookup Field misfeature. The table actually contains
a numeric ID but what you SEE is the trainer name; searching the field using a
name as a criterion won't work, since "Einstein, A." is not equal to 315.
I may be prompting wrong, but I need to pull up the reports by trainer,
course code and date. I copied the prompts from a database someone else had
created.
Click to expand...

Well... unless your table has the same fieldnames and fieldtypes, this may not
work. You've got to have at least some knowledge of what code is doing before
you blindly copy it!
I'm getting a syntax error message when I try the expression below. I'm
using it in place of
HAVING (((tblcourse.trainer) Like [Trainer Name]) AND
((tblRetirementRespondentold.coursedate) Between [Beginning Date] And [Ending
Date]) AND ((tblRetirementRespondentold.coursenumber)=[Forms]![ENTER COURSE
NUMBER]![coursenumber]));
Click to expand...

Please (again) open the query in full SQL view and post it here.
Has anyone told you lately that you have the patience of a saint?
Especially since you're answering my questions! :-D
Click to expand...

The accusation has been made before... but not by my family... <g>

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

Here is the code again. I used the expression you said to use earlier, but
it doesn't like when I change HAVING to WHERE.
Click to expand...

ah... my mistake. The WHERE clause comes *before* the Order By, you have it
after.

Try

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.coursedate = tblRetirementRespondentold.coursedate) AND
(tblcourse.coursenumber = tblRetirementRespondentold.coursenumber) AND
(tblcourse.trainer = tblRetirementRespondentold.trainer)
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]))
GROUP BY tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class;

You can also do this in the query grid by changing the Totals row default
Group By operator to Where.

John W. Vinson [MVP]
 
G

Guest

I changed the query grid and used =Abs([SumOfQ1excellent]) and it WORKED!!!!
All of my negative numbers are now positive!!!

Massive dancing in the streets.... Balloons, streamers and confetti coming
down from the sky.... Actually, it's more like me dancing at my desk and
taking a slug of tea. :-D

THANK YOU THANK YOU THANK YOU!!!! YOU ARE A GOD!!
--
Anita :-DDDDDD


John W. Vinson said:
Here is the code again. I used the expression you said to use earlier, but
it doesn't like when I change HAVING to WHERE.
Click to expand...

ah... my mistake. The WHERE clause comes *before* the Order By, you have it
after.

Try

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.coursedate = tblRetirementRespondentold.coursedate) AND
(tblcourse.coursenumber = tblRetirementRespondentold.coursenumber) AND
(tblcourse.trainer = tblRetirementRespondentold.trainer)
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]))
GROUP BY tblcourse.trainer, tblRetirementRespondentold.coursedate,
tblRetirementRespondentold.coursenumber, tblcourse.class;

You can also do this in the query grid by changing the Totals row default
Group By operator to Where.

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

THANK YOU THANK YOU THANK YOU!!!! YOU ARE A GOD!!
Click to expand...

<hastily putting a lightning rod on the roof and praying for forgiveness>

Glad I was able to help. Divinity has nothing to do with it - twenty years'
experience with databases has a lot more! <g>

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtracting positive amts from negative and positive from positive 3
Change positive number to negative number 1
Negative number alignment 1
Change Negative number to a positive number 3
Conditional Negative Number 1
Sum not recoginzing a negative number 5
change a positive number to negative 16
Subtract positive number from negative number 4

Top