Help__
i am trying to query data in access database by this sql command but i alway get the error like this "
The select statement includes a reserved word or an argument that is misspell or missing, or the punctuation is incorrect"
Note: The error is occurring just when i run program but if i copy the sql command(when i debug) to execute in MS access, it's no problem
Anybody help me to check?
Here is my sqlcommand:
cmd = New OleDbCommand("TRANSFORM iif(isnull(count([tb_working_info].[working_state])),0,1) AS [status] SELECT ([tb_employee].[emp_name]+' '+[tb_employee].[emp_lastname]) AS ชื่อ, [tb_project].[pro_name] AS โครงการ,[w].[work] as ทำงาน,[s].[sick] as ป่วย,[lh].[leftH] as [ลาครึ่งวัน],[lf].[leftF] as [ลาเต็มวัน],[l].[leave] as [ขาด],iif([tb_employee].[emp_type]='รายเดือน', [tb_employee].[emp_wage]- iif([sY].[sickY]>12,([s].[sick] - 12)*([tb_employee].[emp_wage] / 30),0)+iif(([lhY].[leftHY] + [lfY].[leftFY])> 6,(((lh.leftH/2)+ [lf].[leftF])-6)*([tb_employee].[emp_wage] / 30),0)+ [l].[leave]*([tb_employee].[emp_wage] / 30),[tb_employee].[emp_wage] *([w].work + ((iif(sY.sickY <= 6,(s.sick - 6),0))))) as เงินเดือน FROM ((((((((((([tb_project] RIGHT JOIN [tb_employee] ON [tb_project].[pro_id] = [tb_employee].[emp_pro_id]) LEFT JOIN tb_working_info ON [tb_employee].[emp_id] = [tb_working_info].[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as sick, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state] = 'ป่วย' and [tb_working_info].[working_date] between #26/" & thisDate.Month - 1 & "/" & thisDate.Year & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id]) s on [tb_working_info].[emp_id]=[s].[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as work, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ทำงาน'and [tb_working_info].[working_date] between #26/" & thisDate.Month - 1 & "/" & thisDate.Year & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])w on [tb_working_info].[emp_id]=[w].[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as leftH, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ลาครึ่งวัน' and [tb_working_info].[working_date] between #26/" & thisDate.Month - 1 & "/" & thisDate.Year & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])lh on [tb_working_info].[emp_id]=lh.[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as leftF, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ลาเต็มวัน' and [tb_working_info].[working_date] between #26/" & thisDate.Month - 1 & "/" & thisDate.Year & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])lf on [tb_working_info].[emp_id]=lf.[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as leave, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ขาด' and [tb_working_info].[working_date] between #26/" & thisDate.Month - 1 & "/" & thisDate.Year & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])l on [tb_working_info].[emp_id]=l.[emp_id]) Left Join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as sickY, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ป่วย' and [tb_working_info].[working_date] between #26/12/" & thisDate.Year - 1 & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id]) sY on [tb_working_info].[emp_id]=sY.[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as workY, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ทำงาน' and [tb_working_info].[working_date] between #26/12/" & thisDate.Year - 1 & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])wY on [tb_working_info].[emp_id]= [wY].[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as leftHY, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ลาครึ่งวัน' and [tb_working_info].[working_date] between #26/12/" & thisDate.Year - 1 & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])lhY on [tb_working_info].[emp_id]=lhY.[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as leftFY, [tb_working_info].[emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ลาเต็มวัน' and [tb_working_info].[working_date] between #26/12/" & thisDate.Year - 1 & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])lfY on [tb_working_info].[emp_id]=[lfY].[emp_id]) left join (select iif(isnull(count ([tb_working_info].[working_state])),0,count ([tb_working_info].[working_state])) as [leaveY], [emp_id] from [tb_working_info] where [tb_working_info].[working_state]='ขาด' and [tb_working_info].[working_date] between #26/12/" & thisDate.Year - 1 & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# group by [tb_working_info].[emp_id])lY on [tb_working_info].[emp_id]=[lY].[emp_id] where [tb_working_info].[working_date] between #26/" & thisDate.Month - 1 & "/" & thisDate.Year & "# and #25/" & thisDate.Month & "/" & thisDate.Year & "# GROUP BY [tb_employee].[emp_name], [tb_employee].[emp_lastname], [tb_project].[pro_name],[s].[sick],[w].[work],[lh].[leftH],[lf].[leftF],[l].[leave],[sY].[sickY],[wY].[workY],[lhY].[leftHY],[lfY].[leftFY],[lY].[leaveY],[tb_employee].[emp_wage],[tb_employee].[emp_type] PIVOT Format([tb_working_info].[working_date],'d') in ('26','27','28','29','30','31','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25')", cn)
Submit an article or tip