Null IIF Statement In Access Query

[johnwiseman]

I have a Query attached to a Table [tbl Main Input]. Two of the table columes are as follows.

ID - Autonumber datatype.
Error - Yes/No datatype

The Query [qry Errors Made] is simple.

ID - Count
ERROR - Group By - Criteria <>0

I have used the Criteria <> in ERROR to filter out the "NO" and leave just the "YES"

When the query is run the ID will return a COUNT of ERROR which are ticked YES. If there are 10 entrys in the table and 4 have errors the ID will display "4". Excellent just what i want it to do.

However If there are 10 entrys and there are no ERROR ticked Yes the ID returnes no value at all. Not what I want.

If there are no ERROR then I want the ID to display a "0".

Thanks in advance for your help...

John

 

[lespaul]

can you post the SQL of your query please?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[johnwiseman]

Hi. Thanks for looking at this.

Here you go.

SELECT [tbl Main Input].[Error Code A], Count([tbl Main Input].ID) AS CountOfID
FROM [tbl Main Input]
WHERE ((([tbl Main Input].Date) Between [forms]![frm mi]![startdate] And [forms]![frm mi]![enddate]))
GROUP BY [tbl Main Input].[Error Code A]
HAVING ((([tbl Main Input].[Error Code A])<>0));


Now you will see that if [Error Code A] has any "YES" ticked then the CountOfID will result in the value. If [Error Code A] has no "YES" (ie all YES/NO unticked)the the CountOfID will produce no result at all. I need it to show a "0"


Thanx

John

 

[lespaul]

try:

SELECT [Error Code A], SUM(iif([Error Code A], 1, 0)) AS CountOfID
FROM [tbl Main Input]
WHERE ((([tbl Main Input].Date) Between [forms]![frm mi]![startdate] And [forms]![frm mi]![enddate]))
WHERE ((([tbl Main Input].[Error Code A])<>0))
GROUP BY [tbl Main Input].[Error Code A];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

My guess:
SELECT Sum(IIf([Error Code A]<>0, 1, 0)) AS CountOfID
FROM [tbl Main Input]
WHERE [Date] Between [Forms]![frm mi]![startdate] And [Forms]![frm mi]![enddate]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnwiseman]

Hi Lespaul.

Input the SQL you suggested.

SELECT [Error Code A], SUM(iif([Error Code A], 1, 0)) AS CountOfID
FROM [tbl Main Input]
WHERE ((([tbl Main Input].Date) Between [forms]![frm mi]![startdate] And [forms]![frm mi]![enddate]))
WHERE ((([tbl Main Input].[Error Code A])<>0))
GROUP BY [tbl Main Input].[Error Code A];

I am getting an error message that there is a missing operator. I have scanned this expression but cant see where the problem is.

Can you assist please.


John

 

[lespaul]

the problems of cut and paste. try this (or PHV's suggestion?)

SELECT [Error Code A], SUM(iif([Error Code A], 1, 0)) AS CountOfID
FROM [tbl Main Input]
WHERE [tbl Main Input].Date Between [forms]![frm mi]![startdate] And [forms]![frm mi]![enddate] AND [tbl Main Input].[Error Code A])<>0
GROUP BY [tbl Main Input].[Error Code A];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi