用这种方法也不行呀,大家快帮帮我呀,我很急呀
SELECT month(receive.sgsj) as month,
sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1
FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='2003' AND receive.cjr='小王'
group by month(receive.sgsj)
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14'
[Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 'sum(iif(zrrd.fc1='未填' or zrrd.fc1='为空',0,clng(zrrd.fc1)) as fc1 FROM zrrd INNER JOIN receive ON receive.ajbh=zrrd.ajbh WHERE year(receive.sgsj)='2003' AND receive.cjr='小王' group by month(receive.sgsj)' 中。
sql="SELECT month(receive.sgsj) as month,
sum(case when zrrd.fc1='未填' or zrrd.fc1='为空' then 0 else clng(zrrd.fc1) end) as fc1
FROM zrrd
INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
group by month(receive.sgsj)"
我的代码是这样的,意思是如果字段为未填或为空,
统计时取0,
否则将其转化为数字统计。
一位大哥说access不支持case when
,请问大家有没有好的解决方法,
sql="SELECT month(receive.sgsj) as month,
sum(iif(zrrd.fc1 not in ('未填','为空'),cint(zrrd.fc1),0)) as fc FROM zrrd
INNER JOIN receive ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
GROUP BY month(receive.sgsj)"
这样可以解决,在数据库版块得到的,也许有的朋友用得着的
sql="SELECT month(receive.sgsj) as month,
sum(case when zrrd.fc1='未填' or zrrd.fc1='为空' then 0 else clng(zrrd.fc1) end) as fc1
FROM zrrd
INNER JOIN receive
ON receive.ajbh=zrrd.ajbh
WHERE year(receive.sgsj)='"&year&"' AND receive.cjr='"&cjr&"'
GROUP BY month(receive.sgsj)"
报错如下:
Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14'
[Microsoft][ODBC Microsoft Access Driver] 语法错误 (操作符丢失) 在查询表达式 'sum(case when zrrd.fc1='未填' or zrrd.fc1='为空' then 0 else clng(zrrd.fc1) end)' 中。