Simple Queury question

  • Thread starter Thread starter Love Buzz
  • Start date Start date
L

Love Buzz

Hi all.

I am trying to count two columns using two variables 11 and "R". I keep
getting "This expression is typed incorrectly, or it is too complex to be
evaluated".

Thanks for your help.

SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator, Count([ANF Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator
HAVING ((([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date])
AND (([ANF Analysis].Queu)=11) AND ((Count([ANF Analysis].Disp))="P"));
 
Count([ANF Analysis].Disp))="P"

A Count will return a number and you are looking for "P" which is text.

Try removing the Count like so:
[ANF Analysis].Disp)="P"
 
No, I got an error message trying to do it that way. Thanks. Any other
suggestions?

Jerry Whittle said:
Count([ANF Analysis].Disp))="P"
Click to expand...
Click to expand...

A Count will return a number and you are looking for "P" which is text.

Try removing the Count like so:
[ANF Analysis].Disp)="P"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Love Buzz said:
Hi all.

I am trying to count two columns using two variables 11 and "R". I keep
getting "This expression is typed incorrectly, or it is too complex to be
evaluated".

Thanks for your help.

SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator, Count([ANF Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator
HAVING ((([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date])
AND (([ANF Analysis].Queu)=11) AND ((Count([ANF Analysis].Disp))="P"));
Click to expand...
Click to expand...
 
You did not say what the error message said.
Try this --
SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Operator, Count([ANF
Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
WHERE ([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date]) AND
[ANF Analysis].Queu=11 AND [ANF Analysis].Disp="P"
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Operator;

If [Queu] is a text field then put quotes around the 11.
--
KARL DEWEY
Build a little - Test a little


Love Buzz said:
No, I got an error message trying to do it that way. Thanks. Any other
suggestions?

Jerry Whittle said:
Count([ANF Analysis].Disp))="P"
Click to expand...

A Count will return a number and you are looking for "P" which is text.

Try removing the Count like so:
[ANF Analysis].Disp)="P"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Love Buzz said:
Hi all.

I am trying to count two columns using two variables 11 and "R". I keep
getting "This expression is typed incorrectly, or it is too complex to be
evaluated".

Thanks for your help.

SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator, Count([ANF Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator
HAVING ((([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date])
AND (([ANF Analysis].Queu)=11) AND ((Count([ANF Analysis].Disp))="P"));
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the response. I got an error message when I tried to run this
query.

A co-worker recommended I use 'Like' in my queuery. But the response comes
back empty. Any suggestions?

SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator, Count([ANF Analysis].Queu) AS CountOfQueu
FROM [ANF Analysis]
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator
HAVING ((([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date])
AND (([ANF Analysis].Queu)=11) AND ((Count([ANF Analysis].Queu)) Like "R"));


KARL DEWEY said:
You did not say what the error message said.
Try this --
SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Operator, Count([ANF
Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
WHERE ([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date]) AND
[ANF Analysis].Queu=11 AND [ANF Analysis].Disp="P"
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Operator;

If [Queu] is a text field then put quotes around the 11.
--
KARL DEWEY
Build a little - Test a little


Love Buzz said:
No, I got an error message trying to do it that way. Thanks. Any other
suggestions?

Jerry Whittle said:
Count([ANF Analysis].Disp))="P"

A Count will return a number and you are looking for "P" which is text.

Try removing the Count like so:
[ANF Analysis].Disp)="P"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi all.

I am trying to count two columns using two variables 11 and "R". I keep
getting "This expression is typed incorrectly, or it is too complex to be
evaluated".

Thanks for your help.

SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator, Count([ANF Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator
HAVING ((([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date])
AND (([ANF Analysis].Queu)=11) AND ((Count([ANF Analysis].Disp))="P"));
Click to expand...
Click to expand...
Click to expand...
 
Analysis of your query shows some problems.

In one place you are checking if the value of Queu is eqaul to 11 (a
number value) and then you try checking if the Count of the field is
equal to "r" a letter. Count is going to be equal to a number.

As a guess, you want the following

SELECT [ANF Analysis].[Rej Date]
, [ANF Analysis].Queu
, [ANF Analysis].Operator
, Count([ANF Analysis].Queu) AS CountOfQueu
FROM [ANF Analysis]

WHERE [ANF Analysis].[Rej Date] Between [Start Date] And [End Date]
[ANF Analysis].Queu = "R"

GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu
, [ANF Analysis].Operator
HAVING COUNT(Queu) = 11

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Love said:
Thanks for the response. I got an error message when I tried to run this
query.

A co-worker recommended I use 'Like' in my queuery. But the response comes
back empty. Any suggestions?

SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator, Count([ANF Analysis].Queu) AS CountOfQueu
FROM [ANF Analysis]
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator
HAVING ((([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date])
AND (([ANF Analysis].Queu)=11) AND ((Count([ANF Analysis].Queu)) Like "R"));


KARL DEWEY said:
You did not say what the error message said.
Try this --
SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Operator, Count([ANF
Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
WHERE ([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date]) AND
[ANF Analysis].Queu=11 AND [ANF Analysis].Disp="P"
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Operator;

If [Queu] is a text field then put quotes around the 11.
--
KARL DEWEY
Build a little - Test a little


Love Buzz said:
No, I got an error message trying to do it that way. Thanks. Any other
suggestions?

:

Count([ANF Analysis].Disp))="P"
A Count will return a number and you are looking for "P" which is text.

Try removing the Count like so:
[ANF Analysis].Disp)="P"
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi all.

I am trying to count two columns using two variables 11 and "R". I keep
getting "This expression is typed incorrectly, or it is too complex to be
evaluated".

Thanks for your help.

SELECT [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator, Count([ANF Analysis].Disp) AS CountOfDisp
FROM [ANF Analysis]
GROUP BY [ANF Analysis].[Rej Date], [ANF Analysis].Queu, [ANF
Analysis].Operator
HAVING ((([ANF Analysis].[Rej Date]) Between [Start Date] And [End Date])
AND (([ANF Analysis].Queu)=11) AND ((Count([ANF Analysis].Disp))="P"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top