2
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?

1

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 &gt; and &lt; 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.

| improve this answer | |
1

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)))
       )
| improve this answer | |
  • Those MONTH(..) = 1 AND MONTH(..) = 3 should be MONTH(..) >= 1 AND MONTH(..) <= 3 – ypercubeᵀᴹ Apr 1 '18 at 15:08
0

this should work

select max( isnull(billno, 0) ) + 1 from rec_all
| improve this answer | |
  • 1
    I think your answer would benefit from some additional exposition. – Erik Feb 21 '17 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.