SQL stmts do not work on SQL server but work on Access

[Crystal via AccessMonster.com]

The following statements does not work on Microsoft SQL server. (ERROR msg:
Incorrect syntax near the keyword 'LEFT'.) But they work on Access (with
table names changed). Why? Thanks in advance.

UPDATE ##service LEFT JOIN ##coverage ON ##service.mem=##coverage.id
SET ##service.status = ‘Covered’
WHERE (##service.dt_filled) Between ##coverage.START_DT And ##coverage.END_DT)
;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The statment is not an SQL standard statement. Access lets you do non
standard SQL. To convert to standard SQL (only one table name in the
UPDATE clause):

UPDATE ##service
SET status = 'Covered'
WHERE EXISTS (SELECT * FROM ##coverage AS C
WHERE ##service.dt_filled BETWEEN C.start_dt AND C.end_dt
AND id = ##service.mem)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDsGYYechKqOuFEgEQLvSQCgkzGZ3RARsVfVE97A5DASnZCKCmkAoLBe
D3ZkB3XDuleheOyq4gCv0nST
=92tL
-----END PGP SIGNATURE-----

 

[coedxiao via AccessMonster.com]

Perfect. Worked. Thanks!!

The statment is not an SQL standard statement. Access lets you do non
standard SQL. To convert to standard SQL (only one table name in the
UPDATE clause):

UPDATE ##service
SET status = 'Covered'
WHERE EXISTS (SELECT * FROM ##coverage AS C
WHERE ##service.dt_filled BETWEEN C.start_dt AND C.end_dt
AND id = ##service.mem)

The following statements does not work on Microsoft SQL server. (ERROR msg:
Incorrect syntax near the keyword 'LEFT'.) But they work on Access (with

[quoted text clipped - 4 lines]

WHERE (##service.dt_filled) Between ##coverage.START_DT And ##coverage.END_DT)
;