Access IIF to SQL Statement convert

Jitendra Shekhawat

I have access query and I am converting it to SQL Server 2008R2. I'm looking for the equivalent to the Access IIF statement in SQL Server.

Below is the statement I am trying to convert;

("select IIF(MAX(bill_bno) IS NULL,1,MAX(bill_bno)+1) from rec_all where rw=0 and iif(month(bill_date)>=1 AND month(bill_date)<=3,year(bill_date)-1,year(bill_date))=" & IIf(Month(Date) >= 1 And Month(Date) <= 3, Year(Date) - 1, Year(Date)))

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
You can use CASE statement.

For example:

("select case when MAX(bill_bno) IS NULL then 1 else MAX(bill_bno) + 1 end
from rec_all
where rw=0
and case when month(bill_date)>=1 AND month(bill_date)<=3 then year(bill_date)-1 else year(bill_date) end = " & IIf(Month(Date) >= 1 And Month(Date) <= 3, Year(Date) - 1, Year(Date)))

Please be generally aware that for checking null, you can also use ISNULL function as in

select isnull(MAX(bill_bno) + 1, 1)
from rec_all
Nitin Sontakke9y ago
It only means you do not have a table named rec_all.

Are you confused between database name and a table name?
Jitendra Shekhawat9y ago
Error Show

Msg 208, Level 16, State 1, Line 1
Invalid object name 'rec_all'.
Jitendra Shekhawat9y ago
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (Intel X86)
Oct 19 2012 13:43:21
Copyright (c) Microsoft Corporation
Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
Nitin Sontakke9y ago
Exactly, I told you that it will work ONLY in SQL Server 2012. You will notice that you are using SQL Server 2008 R2.

In 2008 R2, you will have to use CASE only.
Nitin Sontakke9y ago
Not sure why I don't see the error when I paste the query.

Are you 100% sure you are using SQL Server 2012? Can you just give the following command and check?

select @@version
Rafnas T P9y ago
try this

(select IIF(MAX(bill_bno) IS NULL,1,MAX(bill_bno)+1) from rec_all where rw=0 and
iif(month(bill_date)>=1 AND month(bill_date)<=3,year(bill_date)-1,year(bill_date))='' and
IIf(Month(Date) >= 1 And Month(Date) <= 3, Year(Date) - 1, Year(Date))='')
Nitin Sontakke9y ago
If it has really helped, please accept one of my replies as an accepted answer. Thank you!
Jitendra Shekhawat9y ago
thank you so much

Now iam doing
Nitin Sontakke9y ago
I put your statement in my local environment and can confirm that there is no "syntax error". See attached...
Nitin Sontakke9y ago
Then I guess I will work as is. But may be you should try it first.
Jitendra Shekhawat9y ago
if i'am want to use this query in sql server 2012 then

how to write this query....???
Nitin Sontakke9y ago
Further to your information IIF is now part of SQL Server beginning version 2012.
Jitendra Shekhawat9y ago
how to change it....
please help...