Pass Through Query Update Error (inner join)

[ILCSP]

Hello, I hope someone can help me with this.


I have a SQL 2000 Database with 2 tables, Inventory and Orders. We use

a Access 2000 database as our front end and I need to update the second

past due date in the Orders table with today's date. This second
date must be updated only if the first date more than 30 days ago and
if the Inventory entry date is less than 3 months ago. We do it by
states, so we also have codes for them. When I run this pass through
query, I get an error telling me that there's an incorrect syntax
near the keyword 'Inner.' I basically copied the same query we
were using in Access and I deleted most of the parenthesis () there and

hoped it would work, but it does not. Here's the pass through query
syntax:


UPDATE dbo.Orders INNER JOIN dbo.Inventory ON dbo.Orders.INVID =
dbo.Inventory.INVID SET Orders.PastDueDate2 = Date WHERE
Orders.PastDueDate1 >(Date-30) AND Inventory.StateCode= 27 AND
Inventory.EntryDate<(Date-120)


Can anyone help me figure this out?


Thanks.

 

[[MVP] S.Clark]

See first post, please.

 

[ILCSP]

Hi Stevie, I tried what you suggested (removing the inner join from the
UPDATE part and place it in the WHERE part and it worked. Thanks!