find and replace data in tables

[qster]

Hi,

I'm trying to figure out how to replace a number within a table column to another number.
ie. What I am trying to do is get MS Access 2000 to find all values equal to 2 in a column and replace it with 10002

I am using the UPDATE Query and have this in it:

UPDATE SMT SET SMT.Transit = replace
("SMT.Transit","2","10002&quot
WHERE (((SMT.Transit)=10002));

However it is not producing what I need.
I'm still getting only 2's if I don't set the criteria to 10002 and if I do set the critera to 10002, I only get 2 rows of data, when there should be a lot more.

any ideas or hints?

Thanks in advance

 

[mikey69]

Why don't you just set the query up in the grid. It takes care of all the syntax - it's so much simpler.

 

[qster]

Hi,

could you explain what you mean by "setting the query in the grid"? do you mean the table criteria? in the query?

 

[mikey69]

yes - you just construct the query in design view. It will start as a select query. convert it to an update query by using the button at the top of the screen. The grid changes slightly, giving you space for an update value and below it lots of boxes to put criteria in.