Incorrect syntax near the keyword 'is' in SQL server vb6 Set rs = con.Execute("select IIF(MAX(bill_bno) IS NULL,1,MAX(bill_bno)+1) from rec_all      where rw=0 and iif(month(bill_date)&amp;gt;=1 AND month(bill_date)&amp;lt;=3,year(bill_date)-1,year(bill_date))=" &amp;amp; IIf(Month(Date) &amp;gt;= 1 And Month(Date) &amp;lt;= 3, Year(Date) - 1, Year(Date)))


how to change vb6 code (MS access) database to SQL Server 2012?
Try this:

SELECT COALESCE(MAX(bill_bno) + 1, 1) AS bill_bno
FROM   rec_all
WHERE  rw = 0
       AND 
       (CASE WHEN MONTH(bill_date) &gt;= 1 AND MONTH(bill_date) &lt;= 3
             THEN (YEAR(bill_date) - 1)
             ELSE YEAR(bill_date) END)
       =
       (CASE WHEN MONTH(Date) &gt;= 1 AND MONTH(Date) &lt;= 3
             THEN (YEAR(Date) - 1)
             ELSE YEAR(Date) END)


or this one:

SELECT COALESCE(MAX(bill_bno) + 1, 1) AS bill_bno
FROM   rec_all
WHERE  rw = 0
       AND 
       (
       (MONTH(bill_date) &gt;= 1 AND MONTH(bill_date) &lt;= 3 AND (YEAR(bill_date) - 1) = (YEAR(Date) - 1))
       OR
       ((MONTH(bill_date) &lt;&gt; 1 OR MONTH(bill_date) &lt;&gt; 3) AND (YEAR(bill_date)) = (YEAR(Date)))
       )