Help! The IIF Statement in a query...

Part of the where clause in my SQL Statement is conditional. The query clause is as follows:

SELECT...

FROM...

WHERE PROJECT.COMPLETED<>-1 AND IIF(PROJECT.COST > RANGE.MINRANGE AND PROJECT.COST<RANGE.MAXRANGE , PROJECTRANGE.PROJECTRANGEID <>0 ,NULL)

I guess I didn't translate the if satement correctly because I always got errors when I tried to preview my report.

Need help analyzing the if statement for me. Thanks in advance.

What exactly are you trying to do?

Besides, the IIF you have is not syntactically correctn. IIF(<condition>, Expression if the condition is TRUE, Expression if the condition is FALSE). What you have is IIF( <condition>, <Condition>, <Value>) which is incorrect.

|||

Thanks for reply ndinakar. Here is what I am trying to do:

In the if statement, if ProjectCompleted is true(-1 means false), and if project.cost is greater than minimum range and less than max range, then the where clause should be like the following:

WHERE PROJECT.COMPLETED<>-1 ANDRANGE.PROJECTRANGEID <>0

If the Project.Cost is out of the range of minimum and max range (greater than max range or less than minimum range), then the if statement should not return anything, and the where clause will be like this:

WHERE PROJECT.COMPLETED<>-1

|||

I think I understand your question only partially. So what do you mean when you say return nothing if cost is out of the range? Do you still want to see those records or they should not be in the result set? You can probabbly put a filter on the record set accodringly.

|||Not sure if this will help but it looks to me like you are mixing your languages. IIF is for use in expressions in reporting services table cells etc. In SQL you have to use IF with BEGIN and END for your conditional statements. Have a look at this link which I found very usefulhttp://www.databasejournal.com/features/mssql/article.php/3361651sql