Incorrect syntax near 'SELECT (month(bill_date) & ', ' & ...

Jitendra Shekhawat

how to change access query to sql server 2012

("SELECT (month(bill_date) & ', ' & year(bill_date)) AS dt, " & _

"count(1) AS nbills, sum(amount) AS tamt, sum(iif(chk_free=1,amount,0)) AS pamt, " & _

"tamt-pamt AS totamt From rec_all Where iif(rec_ref is null,0,rec_ref)<>1 and rw = 0 and bill_date " & _

"between '" & Format(DTfrom(0).Value, "yyyy/mm/dd") & "' and '" & Format(DTfrom(1).Value, "yyyy/mm/dd") & "' and co_code='" & CoCode & "'" & _

"GROUP BY year(bill_date), month(bill_date)")

7 Replies

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

Nitin Sontakke9y ago
Accepted answer
I notice one thing, you cannot use tamt and pamt in the SQL Query....

You have to put the formulas again. Let me give you a hint....

Following is NOT allowed:
select sum(a) as b, sum(c) as d, b - d as e
If you have to the above you will have to write as follows:
select sum(a) as b, sum(c) as d, sum(a) - sum(c) as e
Hope you get the point.
Nitin Sontakke9y ago
Remember your following question:

http://www.c-sharpcorner.com/forums/how-to-change-ms-access-query-to-sql-server-2012

See the queries attached to it by you. SQL Server doesn't understand your variables like DTfrom(0), DTfrom(1), CoCode, etc.

So you will need to replace it with the actual values of these variables.
Jitendra Shekhawat9y ago
How to give the expression here.
Nitin Sontakke9y ago
Remeber how you have given queries to me before?

You should show the query that way ONLY. Do not show the expression here.
Jitendra Shekhawat9y ago
Sql Server error show

Incorrect syntax near '1'.

AND HoW TO USE SUM() AS....
Abhilash J A9y ago
1. The & inside the "SELECT ... " should be +
2. You might also need to convert the month and year into varchar to avoid the error "Conversion failed when converting the varchar value '3, ' to data type int."

Example:
SELECT CONVERT(VARCHAR(2),MONTH(bill_date)) +', ' + CONVERT(VARCHAR(4),YEAR(bill_date))
Nitin Sontakke9y ago
Where have you stuck again? As previously mentioned, please paste the VALUE of above expression.

Query as seen by the SQL Server.