How to change MS ACCESS QUERY to SQL server 2012

Jitendra Shekhawat

"SELECT b.trn_sno, b.bill_date, b.bill_bno, b.pt_name, b.age, b.sex, iif(d.doct_nm Is Null,'',d.doct_nm), " & _

"b.amount, iif(b.chk_free=1,b.amount,0), iif(b.chk_free=1,0,b.amount), iif(iif(b.app_ref is null,0,b.app_ref)=1,iif(iif(b.rec_ref is null,0,b.rec_ref)=1,'Refunded','Approve for Refund'),'') FROM rec_all as b LEFT JOIN doctors as d ON b.doct_id=d.doct_id WHERE " & txtfld & " and " & _

"b.rw=0 and b.co_code='" & CoCode & "' ORDER BY b.rec_ref, b.bill_date, b.trn_sno"

15 Replies

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

Nitin Sontakke9y ago
Accepted answer
See whatever you have above MUST BE assigned to a string variable and THEN that variables content will execute in SQL Server.

So it will be something like:

String query = "SELECT b.trn_sno, b.bill_date, b.bill_bno, b.pt_name, b.age, b.sex, iif(d.doct_nm Is Null,'',d.doct_nm), b.amount, iif(b.chk_free=1,b.amount,0), iif(b.chk_free=1,0,b.amount), iif(iif(b.app_ref is null,0,b.app_ref)=1,iif(iif(b.rec_ref is null,0,b.rec_ref)=1,'Refunded','Approve for Refund'),'') FROM rec_all as b LEFT JOIN doctors as d ON b.doct_id=d.doct_id WHERE " & txtfld & " and b.rw=0 and b.co_code='" & CoCode & "' ORDER BY b.rec_ref, b.bill_date, b.trn_sno";

This will have the query ready for SQL Server in variable query. The contents of query variable can be executed in SQL Server.

Hope it clarifies. If not, please do let me know.
Jitendra Shekhawat9y ago
MY Contact no 09982499436 whatapp also

can you tell me on team viewer
Nitin Sontakke9y ago
Also can you please confirm that the data type of the bill_date column is actually date?

Also try following query:
SELECT b.trn_sno
  ,b.bill_date
  ,b.bill_bno
  ,b.pt_name
  ,b.age
  ,b.sex
  ,iif(d.doct_nm Is Null,'',d.doct_nm)
  ,b.amount
  ,iif(b.chk_free = 1, b.amount, 0)
  ,iif(b.chk_free = 1, 0, b.amount)
  ,iif(iif(b.app_ref is null, 0, b.app_ref) = 1, iif(iif(b.rec_ref is null, 0, b.rec_ref) = 1, 'Refunded', 'Approve for Refund'), '')
FROM rec_all as b
LEFT JOIN doctors as d ON b.doct_id = d.doct_id
WHERE 1 = 1
  and b.bill_date between '20170228' and '20170228'
  and b.rw = 0
  and b.co_code = '01'
ORDER BY b.rec_ref, b.bill_date, b.trn_sno
Nitin Sontakke9y ago
You have 6 queries. Let us concentrate on just one for the moment. Choose the most complex one and please tell me the number.

So I will modify that and post it here.
Jitendra Shekhawat9y ago
removing / from date formating. So date should just be yyyyMMdd or 20170228 only in single quote.

But Result Not Show Number Wise
Nitin Sontakke9y ago
Try removing / from your date formating. So your date should just be yyyyMMdd or 20170228 only in single quote.
Jitendra Shekhawat9y ago
Error is Same.....

Debug.Print result show in text file
Nitin Sontakke9y ago
You please post string with values. It is important to SEE what SQL Server sees not you. Hope you understand.
I think there is a space between date and single quote. Use following string:

" (b.bill_bno>=" & noFrom.Text & " AND b.bill_bno<=" & noTo.Text & ") and bill_date between '" & Format(mainmenu.DTPicker(0), "yyyy/mm/dd") & "' and '" & Format(mainmenu.DTPicker(1), "yyyy/mm/dd") & "'"

I think these minor issues and you should start resolving these on your own.
Jitendra Shekhawat9y ago
Please tell Last qustion

MS Access Code FOR Seach Records Number Wise :-

" (b.bill_bno>=" & noFrom.Text & " AND b.bill_bno<=" & noTo.Text & ") and bill_date between #" & Format(mainmenu.DTPicker(0), "yyyy/mm/dd") & "# and #" & Format(mainmenu.DTPicker(1), "yyyy/mm/dd") & "#"

SQL SERVER CODE FOR bill NumberWise Search

" (b.bill_bno>=" & noFrom.Text & " AND b.bill_bno<=" & noTo.Text & ") and bill_date between ' " & Format(mainmenu.DTPicker(0), "yyyy/mm/dd") & " ' and ' " & Format(mainmenu.DTPicker(1), "yyyy/mm/dd") & " ' "

Result Not Show...
Nitin Sontakke9y ago
Just replace # with single quote and you should be fine.

If not, please do let me know.
Jitendra Shekhawat9y ago
Please tell only one qustion

in Ms Access Query is :-
between two date

{ date between #" & Format(DTfrom(0), "yyyy/mm/dd") & "# and #" & Format(DTfrom(1), "yyyy/mm/dd") & "#" }

now i want to this query in sql server

How to use # in Sql server
AND # not use in sql server then what is use .....
Nitin Sontakke9y ago
Can you please tell me the values of txtfld and CoCode? I think there are only two variables.
Please try to understand. SQL Server doesn't understand these at all. You will need to replace their values at the place of their names and then execute. No double quotes no &.
Jitendra Shekhawat9y ago
errors show in sql server :-

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '&'.
Jitendra Shekhawat9y ago
errors show in sql server

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '&'.
Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'b.amount, iif(b.chk_free=1,b.amount,0), iif(b.chk_free=1,0,b.amount), iif(iif(b.app_ref is null,0,b.app_ref)=1,iif(iif(b.rec_ref' is too long. Maximum length is 128.
Nitin Sontakke9y ago
Have you already tried? As discussed before, I guess, I told that iif will work as is.

Are there any specific errors? If yes, please share.