Update Query

[mike718]

I am having a problem with an update query. Everytime I run this query:

UPDATE STATESTREETRECON
SET STATESTREETRECON.CUSIP = "CASH"
WHERE STATESTREETRECON.CUSIP = CASHCODE.CODE

I am prompted to Enter Parameter Value for CashCode.Code.
I have about 30 different items listed under Code in the Cashcode table.

I also tried to run this query:

UPDATE S
SET S.CUSIP = 'CASH'
FROM STATESTREETRECON S, CASHCODE C
WHERE S.CUSIP = C.CODE

which works in SQL Server but when i run it in Access I get the following message:
Syntax error (missing operator) in qury expression "Cash" from STATESTREETRECON S.

I am trying to prevent the propmting of the message so that the query just automatically compares the cusip to the code and update the appropriate values. Can someone please assist me with this problem.

Mike

 

[PHV]

You may try this:
UPDATE STATESTREETRECON INNER JOIN CASHCODE ON STATESTREETRECON.CUSIP = CASHCODE.CODE
SET STATESTREETRECON.CUSIP = "CASH";
Or this:
UPDATE STATESTREETRECON
SET STATESTREETRECON.CUSIP = "CASH"
WHERE STATESTREETRECON.CUSIP IN (SELECT CASHCODE.CODE FROM CASHCODE);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mike718]

thanks PH. That did the trick. Did not think about using inner joins. Thought I would get the same results.