这条SQL语句怎么转换成ACCESS语句呢？

[WOFEIYANG]

UPDATE ZW_KMDM SET GRADE = ISNULL((SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = ISNULL(ZW_DMFJ.DMCD,0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0')
FROM ZW_KMDM WHERE DWDM = '3301' and nyear ='2006' and item_name = ''

[OracleRoob]

用dsum()。


update zw_kmdm set grade =
nz(dsum('dmfj','zw_dmfj','grade = isnull(zw_dmfj.dmcd,0) and lbbs = 1 and dwdm = 3301 and nyear=2006 and item_name=3301'),'0')
from zw_kmdm
where dwdm = '3301'
and nyear ='2006'
and item_name = ''

[OracleRoob]

--改：
update zw_kmdm set grade =
nz(dsum('dmfj','zw_dmfj','grade = nz(dmcd,0) and lbbs = 1 and dwdm = 3301 and nyear=2006 and item_name=3301'),'0')
from zw_kmdm
where dwdm = '3301'
and nyear ='2006'
and item_name = ''

[WOFEIYANG]

update zw_kmdm set grade =
nz(dsum('dmfj','zw_dmfj','grade = nz(dmcd,0) and lbbs = 1 and dwdm = 3301 and nyear=2006 and item_name=3301'),'0')
from zw_kmdm --ACCESS中的UPDATE语法中好像不用这条语句的
where dwdm = '3301'
and nyear ='2006'
and item_name = ''

执行错误：nz()函数未定义

[wwwwb]

UPDATE ZW_KMDM SET GRADE =
iif(ISNULL(SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0')
WHERE DWDM = '3301' and nyear ='2006' and item_name = ''

[WOFEIYANG]

UPDATE ZW_KMDM SET GRADE = ISNULL((SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = ISNULL(ZW_DMFJ.DMCD,0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0')
FROM ZW_KMDM WHERE DWDM = '3301' and nyear ='2006' and item_name = ''


其中 这条语句WHERE GRADE = ISNULL(ZW_DMFJ.DMCD,0)中的GRADE 是ZW_KMDM表的。ZW_DMFJ表中没有这个字段。上面语句在SQL中通过。现要将其改成ACCESS语句。

[OracleRoob]

--try
update zw_kmdm set grade =
dsum('dmfj','zw_dmfj','grade = iif(isnull(dmcd),0,dmcd) and lbbs = 1 and dwdm = 3301 and nyear=2006 and item_name=3301')
where dwdm = '3301'
and nyear ='2006'
and item_name = ''

[wwwwb]

UPDATE ZW_KMDM SET GRADE =
iif(ISNULL(SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0',
SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'))
WHERE DWDM = '3301' and nyear ='2006' and item_name = ''

[WOFEIYANG]

--try
update zw_kmdm set grade =
dsum('dmfj','zw_dmfj','grade = iif(isnull(dmcd),0,dmcd) and lbbs = 1 and dwdm = 3301 and nyear=2006 and item_name=3301')
where dwdm = '3301'
and nyear ='2006'
and item_name = ''
错误提示：参数不足期待是1。去掉'grade = iif(isnull(dmcd),0,dmcd)执行正确。但是这条WHere 子句是需要的。其中GRADE是ZW_KMDM表的。

使用：
UPDATE ZW_KMDM SET GRADE =
iif(ISNULL(SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0')
WHERE DWDM = '3301' and nyear ='2006' and item_name = ''
错误提示：
用于函数参数的个数不对在查询表达示IIF（ISNULL（SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0')中

[wwwwb]

UPDATE ZW_KMDM SET GRADE =
iif(ISNULL(SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0',
(SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'))
WHERE DWDM = '3301' and nyear ='2006' and item_name = ''

[WOFEIYANG]

wwwwb() 我这样写它提示我，操作必须使用一个可更新的查询。其中我这两张表中当前多没数据。
UPDATE ZW_KMDM SET GRADE =
iif(ISNULL((SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0,ZW_DMFJ.DMCD) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301')),'0',
(SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE GRADE = iif(ISNULL(ZW_DMFJ.DMCD),0,ZW_DMFJ.DMCD) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'))
WHERE DWDM = '3301' and nyear ='2006' and item_name = '';

[WOFEIYANG]

UPDATE ZW_KMDM SET GRADE = ISNULL((SELECT DMFJ FROM ZW_DMFJ AS ZW_DMFJ
WHERE ZW_KMDM.GRADE = ISNULL(ZW_DMFJ.DMCD,0) AND LBBS = 1 AND DWDM = '3301' and nyear='2006' and Item_name='3301'),'0')
FROM ZW_KMDM WHERE DWDM = '3301' and nyear ='2006' and item_name = ''

[OracleRoob]

用dsum()，详细信息查看Access帮助。

[WOFEIYANG]

update zw_kmdm set grade =
dsum('dmfj','zw_dmfj','lbbs = 1 and dwdm = 3301 and nyear=2006 and item_name=3301 AND iif(isnull(dmcd),0,DMCD)= '&ZW_KMDM.grade )
where dwdm = '3301'
and nyear ='2006'
and item_name = '' ;
谢谢各位的回答，这样写就可以了。不过wwwwb() 的方法按理说也是可以用的。可能跟我开发环境相关。因为我是用PB连ACCESS库再在PB中专门执行SQL语句的工具中执行ACCESS的。

[WOFEIYANG]

该结帖了。不过分数不多。感谢你们的回答。呆会儿可能还会有问题。因为好多SQL语句多要转换成ACCESS语句。