Simple Queury question

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"));
 
J

Jerry Whittle

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"
 
L

Love Buzz

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

KARL DEWEY

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

Love Buzz

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

John Spencer

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

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

Data Type Mismatch 2
Expression is to complicated? 7
Simple Select Query - To Complicated? 2
Need Help with Totals Query 24
Prove multicollinearity by regressing one X on ALL the others? 1
Create a Query that Prompts for Date and Time 4
Date Range with Total Count Query 2
ACCESS query expression that works on one computer but not another 4

Top