请教一句SQL，谢谢。

[sqllong]

表结构：
tableA字段：bz,jh,rq
tableB字段：bz,jh,rq

要求：根据条件，用tableB的bz字段更新tableA的bz的值；
问题：以下代码在SQL Server 2000 下能顺利执行，但把代码改写为Access下是SQL就报错；

update tableA 
set tableA.bz=tableB.bz
from tableA inner join tableB on tableA.jh = tableB.jh and tableA.rq = tableB.rq and
tableB.rq>='2008-4-1' and tableB.rq<='2008-4-9'

Access下是SQL语句：

update tableA 
set tableA.bz=tableB.bz
from tableA inner join tableB on tableA.jh = tableB.jh and tableA.rq = tableB.rq and
tableB.rq>=#2008-4-1# and tableB.rq<=#2008-4-9#

报错内容：语法错误（操作符丢失）

谢谢大家了，在线~~~~

[xiaomeixiang]

数值型才能用DSUM，用DLookUp比较好，更正下：

UPDATE tableA SET bz = dlookup("bz","tableB","rq>=#2008-4-1# and rq <=#2008-4-9# AND jh='" & tableA.jh & "' AND rq=#" & tableA.rq & "#"); 

[正宗老冉]

楼上正解。

ANSI-SQL 92

[xiaomeixiang]

UPDATE tableA SET bz = dsum("bz","tableB","rq>=#2008-4-1# and rq<=#2008-4-9# AND jh='" & tableA.jh & "' AND rq=#" & tableA.rq & "#");

[chuifengde]

update tableA,tableB
set tableA.bz=tableB.bz
where tableA.jh = tableB.jh and tableA.rq = tableB.rq and
tableB.rq>=#2008-4-1# and tableB.rq<=#2008-4-9#

[jinjazz]

Access中不允许用where   tableA.fieldA   =   TableB.fieldA   来做update的连接，这是T-sql的语法，必须把t-sql的语法转成Asni的语法，用   join   来做。   
    
  对查询结果关联，access必须把查询分成两部分，最后把临时表删：   
  -------------------------------------------------------------------------   
  select   fid,sum(sl)   as   sumsl   from   aa   into   bb   where   bs=1   group   by   fid;   
    
  update   aa   inner   join     bb   
  on   aa.id=bb.fid   
  set   aa.sl=bb.sumsl   
    
  drop   table   bb