find replace query

[qster]

Hi,

I'm trying to get a query to update or replace a number within a field/column with MS Access.

I could do this step manually using the find/replace tool, but I would like to make a query or module that will automate this process to save me time in the future.

What I am trying to do is tell the MS Access to find a value within a field/column, find and replace that number.

e.g. I'd like to find all values that are "2" within a field and replace it with "3".

I've tried using the Update function or creating a module to code it but each time I'll get a syntax error or somthing else.

I've tried using:
UPDATE SMT SET SMT.Transit = 10002
WHERE ((SMT.Transit) is 2);

Any thoughts?

 

[mrf1xa]

Hi

The following is used in one of my db's to update a field called 'Region' in 'tblRegions' from '01' to '06'. You should be able to tweak it to suit..

UPDATE tblRegions SET tblRegions.Region = "06"
WHERE (((tblRegions.Region)="01&quot);

HTH Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[qster]

Hi Nigel,

Thanks..
problem is I get a syntax error and it's racking my brain trying to figure it out.

this is what I am using;

SELECT SMTBranch2C.Transit, SMTBranch2C.Month, SMTBranch2C.Year, SMTBranch2C.[BEP Code], SMTBranch2C.[Product Code], SMTBranch2C.Volume
FROM SMTBranch2C
UPDATE SMTBranch2C SET SMTBranch2C.Transit = "10002"
WHERE (((SMTBranch2C.transit)="2&quot);

The error I am getting is from the "FROM" statement.

 

[qster]

i've ran the update correctly this time (figured where to use the UPDATE), but now I'm getting a Data type mismatch in the criteria expression.

any thoughts?