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

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

Since you only gave us the line that generates your recordset, we’ll assume that you are able to establish your connection successfully.

First: the following statement should be valid in SQL Server 2012:

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(GETDATE())>= 1 And Month(GETDATE()) <= 3, Year(GETDATE()) - 1, Year(GETDATE()))

As far as I can tell, this is what you listed above, with the HTML-ification removed, and the SQL Server current date and time function GETDATE() replacing what I believe is the MS Access current date function Date.

I’ve displayed the query on multiple lines with indenting for human readability; you can put the full query on one line, if that’s what you’re language requires.

Not knowing the language you’re using, I can’t tell you how to properly format your actual con.execute command; change > and < to > and < if required.

As the other answers showed, there are other commands in SQL more commonly used to do the things you’re doing (I had to check to be sure iif was available in T-SQL), but the major problem query-wise seems to have been how to get the current system date.

Try this:

SELECT COALESCE(MAX(bill_bno) + 1, 1) AS bill_bno
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)
       =
       (CASE WHEN MONTH(Date) >= 1 AND MONTH(Date) <= 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) >= 1 AND MONTH(bill_date) <= 3 AND (YEAR(bill_date) - 1) = (YEAR(Date) - 1))
       OR
       ((MONTH(bill_date) <> 1 OR MONTH(bill_date) <> 3) AND (YEAR(bill_date)) = (YEAR(Date)))
       )

this should work

select max( isnull(billno, 0) ) + 1 from rec_all