Access IIF to SQL Statement convert

Question

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)))

Nitin Sontakke · 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) = 1 And Month(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 Sontakke · Answer

I put your statement in my local environment and can confirm that there is no "syntax error". See attached...

Nitin Sontakke · Answer

It only means you do not have a table named rec_all. Are you confused between database name and a table name?

Jitendra Shekhawat · Answer

Error Show Msg 208, Level 16, State 1, Line 1 Invalid object name 'rec_all'.

Jitendra Shekhawat · Answer

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 Sontakke · Answer

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 Sontakke · Answer

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 P · Answer

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) IIf(Month(Date) >= 1 And Month(Date)

Nitin Sontakke · Answer

If it has really helped, please accept one of my replies as an accepted answer. Thank you!

Jitendra Shekhawat · Answer

thank you so much Now iam doing

Nitin Sontakke · Answer

Then I guess I will work as is. But may be you should try it first.

Jitendra Shekhawat · Answer

if i'am want to use this query in sql server 2012 then how to write this query....???

Nitin Sontakke · Answer

Further to your information IIF is now part of SQL Server beginning version 2012.

Jitendra Shekhawat · Answer

how to change it.... please help...

Access IIF to SQL Statement convert

Answers (14)