Simple Select Query - To Complicated?

[Love Buzz]

Hello everyone.

It seems like I am posting daily these days. I have only been using MS
Access since Novemberish and I appreciate all of the assistance I get when I
post.

Today's issue is just a simple query trying to count only those fiels in my
table (errors) that equal Y. When I run the query I get the message 'the
expression is typed incorrectly, or it is too complex to be evaluated.'

Here is my SQL. Thank you for your assistance.

SELECT Errors.[User ID], Count(Errors.[Error Indicator]) AS [CountOfError
Indicator], Count(Errors.[Customer Impact?]) AS [CountOfCustomer Impact?],
Count(Errors.[Loss?]) AS [CountOfLoss?], Count(Errors.[Caught on Quality
Check?]) AS [CountOfCaught on Quality Check?]
FROM Errors
WHERE (((Errors.[Notification Date]) Between [Start Date] And [End Date]))
GROUP BY Errors.[User ID]
HAVING (((Count(Errors.[Customer Impact?]))="Y") AND
((Count(Errors.[Loss?]))="Y") AND ((Count(Errors.[Caught on Quality
Check?]))="Y"));

 

[John Spencer]

Count(somefield) is always going to return a NUMBER. A Number cannot be
compared to "Y"

SELECT Errors.[User ID]
, Count(Errors.[Error Indicator]) AS [CountOfError Indicator]
, Count(IIF(Errors.[Customer Impact?]="Y",1,Null)) AS [CountOfCustomer Impact?]
, Count(IIF(Errors.[Loss?]="Y",1,Null)) AS [CountOfLoss?]
, Count(IIF(Errors.[Caught on Quality Check?]="Y",1,Null)) AS [CountOfCaught
on Quality Check?]
FROM Errors
WHERE (((Errors.[Notification Date]) Between [Start Date] And [End Date]))
GROUP BY Errors.[User ID]


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

 

[Love Buzz]

Thank you sir. That did the trick.

Count(somefield) is always going to return a NUMBER. A Number cannot be
compared to "Y"

SELECT Errors.[User ID]
, Count(Errors.[Error Indicator]) AS [CountOfError Indicator]
, Count(IIF(Errors.[Customer Impact?]="Y",1,Null)) AS [CountOfCustomer Impact?]
, Count(IIF(Errors.[Loss?]="Y",1,Null)) AS [CountOfLoss?]
, Count(IIF(Errors.[Caught on Quality Check?]="Y",1,Null)) AS [CountOfCaught
on Quality Check?]
FROM Errors
WHERE (((Errors.[Notification Date]) Between [Start Date] And [End Date]))
GROUP BY Errors.[User ID]


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

Hello everyone.

It seems like I am posting daily these days. I have only been using MS
Access since Novemberish and I appreciate all of the assistance I get when I
post.

Today's issue is just a simple query trying to count only those fiels in my
table (errors) that equal Y. When I run the query I get the message 'the
expression is typed incorrectly, or it is too complex to be evaluated.'

Here is my SQL. Thank you for your assistance.

SELECT Errors.[User ID], Count(Errors.[Error Indicator]) AS [CountOfError
Indicator], Count(Errors.[Customer Impact?]) AS [CountOfCustomer Impact?],
Count(Errors.[Loss?]) AS [CountOfLoss?], Count(Errors.[Caught on Quality
Check?]) AS [CountOfCaught on Quality Check?]
FROM Errors
WHERE (((Errors.[Notification Date]) Between [Start Date] And [End Date]))
GROUP BY Errors.[User ID]
HAVING (((Count(Errors.[Customer Impact?]))="Y") AND
((Count(Errors.[Loss?]))="Y") AND ((Count(Errors.[Caught on Quality
Check?]))="Y"));