IIF function in SQL Jet.

[rann]

hi,
I am using an 'IFF' function in a Query as below.

SELECT ID,IIf([LectureORLab]='Lecture',[Hours],Null) AS Lectures, IIf([LectureORLab]='Lab',[Hours],Null) AS Labs FROM Courses;

And it worked fine as a Access Query.I used the same query in my (both)ADO,DAO code.i was getting an error.

There is no problem with my ADO,DAO code since it works when i remove the 'IFF' statement and use table fields.


I used the query in ADO to create a recordset and in DAO i
was using dbs.CreateQueryDef("qryMyQuery", SQL) to create
a query using code.

it was not working in both cases.

Does ADO,DAO recognize 'iff' function.if so how do we
represent it in ADO,DAO(SQL JET).

Any help or Links would be helpfull,


Thanks,
Rann.

 

[cmmrfrds]

Are you using the Access Database or another database backend?

 

[rann]

Hi,
I am using SQL Server as the Backend.Access and SQLServer are connected using ODBC.

Actualy when i used DAO to create query using
dbs.CreateQueryDef("qryMyQuery", SQL) the Iff function was working fine.

The problem is with ADO recordset.

Thanks for you help,

Rann.

 

[cmmrfrds]

I have not used DAO for a couple of years, but it may interpet the IIF before passing the query on to SQL Server. Microsoft must have decided not to do this in ADO, since the IIF is not valid syntax in SQL Server. It is Microsoft specific for Jet/Access mdb.

The counterpart to the IIF in SQL Server is the CASE statement. I suggest looking up the syntax in BOL which is the help files for SQL Server. These can be downloaded from Microsoft and are good to have if you are going to be working with SQL Server. I use the help for SQL Server 7 since it is more concise and easier to traverse than 2000 help. Here are links to both.

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

http://www.microsoft.com/sql/techinfo/productdoc/70/books.asp

Example of CASE Statement
Select Site,
sum(Case When [Month] = 1 Then AvgProd Else 0 End) As M1,
sum(Case When [Month] = 2 Then AvgProd Else 0 End) As M2,
sum(Case When [Month] = 3 Then AvgProd Else 0 End) As M3,
sum(Case When [Month] = 4 Then AvgProd Else 0 End) As M4,
sum(Case When [Month] = 5 Then AvgProd Else 0 End) As M5,
sum(Case When [Month] = 6 Then AvgProd Else 0 End) As M6,
sum(Case When [Month] = 7 Then AvgProd Else 0 End) As M7,
sum(Case When [Month] = 8 Then AvgProd Else 0 End) As M8,
sum(Case When [Month] = 9 Then AvgProd Else 0 End) As M9,
sum(Case When [Month] = 10 Then AvgProd Else 0 End) As M10,
sum(Case When [Month] = 11 Then AvgProd Else 0 End) As M11,
sum(Case When [Month] = 12 Then AvgProd Else 0 End) As M12
From (
Select site, mnth, avg(productivity) AvgProd
from TableName Where Site In ('City', 'Country')
Group By site, Mnth) As qry
Group By Site
Order By Site