IIF() in WHERE clause

[jluost1]

I have seen several tread posted here about IIF(). I found none of them can solve my problem.

My access sql statements is like the following:

SELECT name FROM customer WHERE IIF(isdate(note),note,'01/01/2050')> #01/01/2001#

The column "note" is text (like varchar(255)), but it can be a date, a numeric value, a boolean value. It depends on the other column "note_type".

I know I can use CASE and WHEN statement if I select "note" column also. Unfortunately, "name" is the only column I can have (simply put,I am using ASP to generate sql and use it dynamically).

Can anyone tell me how to get around IIF() function in WHERE clause?

 

[rajeevnandanmishra]

Hi jluost1,
You are correct that you have to use Case . When. syntax. And, I don't this that your statement I know I can use CASE and WHEN statement if I select "note" column also. is true.
You can use anything/ everything in your where clause, irrespective you are selecting it in select or not. And if you face any problem using 'case when' please let us know with the sql statement and particular error you are getting.

 

[jluost1]

Hi, rajeevnandanmishra,

Thank you for your quick response. Following is the statement I am trying to run:

SELECT name FROM customer WHERE
(SELECT CASE
WHEN isdate([note]) CONVERT(datatime,[note])
ELSE CONVERT(datetime,'01/01/2001')
END) > CONVERT(datetime,'06/01/2001')

Following is the error message:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CONVERT'.

 

[rajeevnandanmishra]

Try this line of code :
SELECT name FROM customer WHERE
(SELECT CASE CONVERT(datetime,note)
WHEN null THEN '01/01/2001'
ELSE note END) > '06/01/2001'

Though i think, you can specify the condition for note_type, in which the note will always be having date values, in that case you don't have to use any of the convert or case function.

 

[tlbroadbent]

You need to include THEN in the CASE statement.

SELECT name FROM customer WHERE
(SELECT CASE
WHEN isdate([note]) THEN CONVERT(datatime,[note])
ELSE CONVERT(datetime,'01/01/2001')
END) > CONVERT(datetime,'06/01/2001')
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jluost1]

Hi, I corrected the syntax and modified it a little bit and it seems to work. These are newbie error.

Thank you two a lot.

=================================

SELECT name FROM customer WHERE
(CASE [note]
WHEN isdate([note]) THEN CONVERT(datetime,[note])
ELSE CONVERT(datetime,'01/01/2001')
END) > CONVERT(datetime,'06/01/2001')