Now that I have help from this forum's,I have the sql command which can produce the correct result in ACCESS. However when I brought that to use in ASP the error said like this..
Microsoft JET Database Engine error '80040e14'
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
what did I do wrong ? my asp code is ….
Dim rsBill
set rsBill = Server.CreateObject("ADODB.Recordset")
rsBill.ActiveConnection = Conn_string<br>
rsBill.Source ="select sum(tbt.bill_total) as bill_total ,iif(sum_vat_total is null, 0, sum_vat_total) as vat_total, iif(sum_vat_total is null, 0, sum_vat_total) + sum(bill_total) as sum_of_all, month(showndate) as month from tbl_bill_total tbt left join (select sum(vat_total) as sum_vat_total , month(showndate) as month from tbl_vat_bill_total where if_paid = true and year(showndate) = 2013 group by month(showndate)) tvt on tvt.month = month(tbt.showndate) where year(showndate) = 2013 group by month(showndate) , sum_vat_total"
rsBill.CursorType = 0
rsBill.CursorLocation = 3
rsBill.LockType = 3
rsBill.Open()
rsBill.PageSize = 20
I've tried for several days to find out what's wrong but still couldn't get it.
The SELECT statement includes month as an alias (in two places) ...
month(showndate) as month
Since there is a function with the same name, month is likely the reserved word the db engine is complaining about. If you want to keep that alias, enclose it in square brackets.
month(showndate) as [month]
You reported the query ran without error in an Access session. In that situation, the query is run from DAO. But it fails when run from ADO because ADO seems to be less forgiving about reserved words.