using IIF and Count together 3

[DougP]

I made this query in Microsoft Access

        SQLCode = "SELECT FieldsToUse AS NeedThisSpace, FieldsToUse AS NeedThisSpace2, FieldsToUse, " _
                  & "Count([Outcome]) AS Total, " _
                  & "Count('IIf([outcome]=1,[outcome],Null)') AS Passed, " _
                  & "Count('IIf([outcome]=-1,[outcome],Null)') AS Failed, " _
                  & "Count('IIf([outcome]=0,[outcome],Null)') AS NA " _
                  & "From Results " _
                  & "GROUP BY FieldsToUse;"

which returns something like so

NeedThisSpace, NeedThisSpace2,Total,Passed,Failed,NA
63 56 6 7

but I can't get it to work in SQL 2000
all I get is a total for all
NeedThisSpace, NeedThisSpace2,Total,Passed,Failed,NA
63 63 63 63

I got this from "Books on line" but are the single quotes around the IIF acting like comments?

TIA

DougP, MCP, A+

 

[hneal98]

When you say SQL 2000, I am assuming you mean SQL Server 2000. In that case, here is how the query would look:

SELECT FieldsToUse AS NeedThisSpace, 
	FieldsToUse AS NeedThisSpace2,
	FieldsToUse,
	Count([Outcome]) AS Total, 
	Count(case when [outcome]=1 then [outcome] else Null end) AS Passed,
             Count(case when [outcome]=-1 then [outcome] else Null end) AS Failed,
 	Count(case when [outcome]=0 then [outcome] else Null end) AS NA,
From 
	Results 
GROUP BY FieldsToUse

 

[SQLBill]

IIF does not exist in SQL Server. That's an Access command.

-SQLBill

 

[DougP]

Thanks
hneal98
just had to remove the comma after NA,

else Null end) AS NA,

SQLBill
I guess you are right the 'Books on line' had mention of it but the example was vague.
and the IIF was in single quotes


DougP, MCP, A+

 

[SQLBill]

You can use it in SQL Server IF and only if you are passing the query to Access. For example, you could create a linked server to an Access database, create a Stored Procedure that ran on SQL Server and sent a command to the Access database. Then you could include the IIF in the SQL Server command as long as it was identified as a string (single quotes). It would get passed to Access and run there.

-SQLBill

 

[hneal98]

Hi SQLBill, That is very interesting to know. Thanks for the info.