Syntax Error

[Jrmrosebud]

This brings up a syntax error:

UPDATE ItemT.D3Qty
SET ItemT.D3Qty - TransactionT.Quantity
WHERE TransactionT.Item = ItemT.Item AND (TransactionT.TransactionType = "Adjustment" OR TransactionT.TransactionType = "Usage");

And when I change it to this:

UPDATE ItemT.D3Qty
SET ItemT.D3Qty = ItemT.D3Qty - TransactionT.Quantity
WHERE TransactionT.Item = ItemT.Item AND (TransactionT.TransactionType = "Adjustment" OR TransactionT.TransactionType = "Usage");

I get the following error:

Could not find file '..........\Documents\ItemT.mdb'.

Am I completely writing this incorrectly? Is there a better way? How can I make this work?

 

[CJ_London]

suggest you use the query builder and see what code it produces for comparison - your second query is closer to the correct syntax - try changing the first line to

UPDATE ItemT

 

[WayneRyan]

Update ItemT
Set ItemT.D3Qty = TransactionT.Quantity
from ItemT, TransactionT
where ItemT.Item =TransactionT.Item And
       TransactionT.Item in ('Adjustment', 'Usage')

Wayne

 

[Jrmrosebud]

Wayne,

That brings up "Syntax error (missing operator) in query expression 'TransactionT.Quantity from ItemT'.

In another post you asked for further detail. I have another thread under General.

http://www.access-programmers.co.uk/forums/showthread.php?t=276518

That might be the explanation you were looking for.

Thank you for your help. I'm still working on ironing out this problem.

Jackie