Can you use iif?

[Todd K.]

In my SQL "INSERT INTO" string, I would like it to insert today's date if
three of the bit fields are all False, but the iif statement doesn't work
(the datToday variable is declared above and outputs correctly in the string):

....,(iif([UFIR]='False' AND [Ethics]='False' AND [CO_OP]='False','" &
datToday & "',' ')) AS Release_Date,...

I keep getting Run-time error '-2147217900 (80040e14)': Line 1: Incorrect
syntax near '='. Can you not use iif statements in SQL?

 

[Robert Morley]

No, there's no such command in SQL. What you want is the CASE statement.

SELECT <OtherFields>, CASE WHEN [UFIR]='False' AND [Ethics]='False' AND
[CO_OP]=FALSE THEN GETDATE() ELSE '' END AS Release_Date, <More Fields> FROM
....etc.

Note that there are actually two variations of the CASE statement...you can
look them up in the SQL help or ask here if you need more info. I've also
used GETDATE() to get the date; that may or may not be appropriate for your
needs, but if you're constructing the string manually, then you can use
datToday instead, as you has originally. Also, I *think* the "AS" in this
case is redundant...SQL Server seems to be a little random about when it
wants one and when it doesn't, and will often remove it after you've typed
it in if you're using the SQL EM View designer.



Rob

 

[Norman Yuan]

In SQL Server's T-SQL, you use CASE WHEN...THEN WHEN...THEN...ELSE...END,
instead of Iif(), which is VBA function in Access, if your query is
originally from Access query.

 

[Todd K.]

Excellent, that works perfectly. Robert, that's twice today you've hooked me
up, thanks!

No, there's no such command in SQL. What you want is the CASE statement.

SELECT <OtherFields>, CASE WHEN [UFIR]='False' AND [Ethics]='False' AND
[CO_OP]=FALSE THEN GETDATE() ELSE '' END AS Release_Date, <More Fields> FROM
....etc.

Note that there are actually two variations of the CASE statement...you can
look them up in the SQL help or ask here if you need more info. I've also
used GETDATE() to get the date; that may or may not be appropriate for your
needs, but if you're constructing the string manually, then you can use
datToday instead, as you has originally. Also, I *think* the "AS" in this
case is redundant...SQL Server seems to be a little random about when it
wants one and when it doesn't, and will often remove it after you've typed
it in if you're using the SQL EM View designer.



Rob

In my SQL "INSERT INTO" string, I would like it to insert today's date if
three of the bit fields are all False, but the iif statement doesn't work
(the datToday variable is declared above and outputs correctly in the
string):

...,(iif([UFIR]='False' AND [Ethics]='False' AND [CO_OP]='False','" &
datToday & "',' ')) AS Release_Date,...

I keep getting Run-time error '-2147217900 (80040e14)': Line 1: Incorrect
syntax near '='. Can you not use iif statements in SQL?

 

[Robert Morley]

No problem. I was where you are now about 2 years ago, so it won't be long
before you're turning around and helping someone else out.


Rob

Excellent, that works perfectly. Robert, that's twice today you've hooked
me
up, thanks!

No, there's no such command in SQL. What you want is the CASE statement.

SELECT <OtherFields>, CASE WHEN [UFIR]='False' AND [Ethics]='False' AND
[CO_OP]=FALSE THEN GETDATE() ELSE '' END AS Release_Date, <More Fields>
FROM
....etc.

Note that there are actually two variations of the CASE statement...you
can
look them up in the SQL help or ask here if you need more info. I've
also
used GETDATE() to get the date; that may or may not be appropriate for
your
needs, but if you're constructing the string manually, then you can use
datToday instead, as you has originally. Also, I *think* the "AS" in
this
case is redundant...SQL Server seems to be a little random about when it
wants one and when it doesn't, and will often remove it after you've
typed
it in if you're using the SQL EM View designer.



Rob

In my SQL "INSERT INTO" string, I would like it to insert today's date
if
three of the bit fields are all False, but the iif statement doesn't
work
(the datToday variable is declared above and outputs correctly in the
string):

...,(iif([UFIR]='False' AND [Ethics]='False' AND [CO_OP]='False','" &
datToday & "',' ')) AS Release_Date,...

I keep getting Run-time error '-2147217900 (80040e14)': Line 1:
Incorrect
syntax near '='. Can you not use iif statements in SQL?