 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / February 2007Can you use iif?
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? 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 [quoted text clipped - 6 lines] > I keep getting Run-time error '-2147217900 (80040e14)': Line 1: Incorrect > syntax near '='. Can you not use iif statements in SQL? 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. > [quoted text clipped - 23 lines] > > I keep getting Run-time error '-2147217900 (80040e14)': Line 1: Incorrect > > syntax near '='. Can you not use iif statements in SQL? 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 [quoted text clipped - 36 lines] >> > Incorrect >> > syntax near '='. Can you not use iif statements in SQL? 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. > 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 [quoted text clipped - 6 lines] > I keep getting Run-time error '-2147217900 (80040e14)': Line 1: Incorrect > syntax near '='. Can you not use iif statements in SQL? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.