Update part of a data string with an Update query -ACC97

[kevinSTS]

Hi.
I'm trying to run an update query in ACC97 to update a data string where only part of the data needs to be changed. Since I don't do this often, I cant for the life of me remember how to do it.

Here'w what the data looks like: 10-1100-120-000-10-10-00-000
I need to change only this portion: 10-1100-120-000-10-10-00-000

Any ideas on how to do this in a query?

Thanks.
Kevin

[ADezii]

Hi.
I'm trying to run an update query in ACC97 to update a data string where only part of the data needs to be changed. Since I don't do this often, I cant for the life of me remember how to do it.

Here'w what the data looks like: 10-1100-120-000-10-10-00-000
I need to change only this portion: 10-1100-120-000-10-10-00-000

Any ideas on how to do this in a query?

Thanks.
Kevin

Assuming the Field containing the data is called [Test], and its length is a consistent 28 characters long, then:

UPDATE tblTest SET tblTest.Test = Left$([Test],8) & "<your 3-digit-value here>"   & Right$([Test],17)
WHERE tblTest.Test Is Not Null;

[kevinSTS]

Assuming the Field containing the data is called [Test], and its length is a consistent 28 characters long, then:

UPDATE tblTest SET tblTest.Test = Left$([Test],8) & "<your 3-digit-value here>"   & Right$([Test],17)
WHERE tblTest.Test Is Not Null;

Thanks! It worked great! Sometimes there's just too much going on in my little head to remember snipets of code!

Greatly appreciated!
Kevin

[ADezii]

Thanks! It worked great! Sometimes there's just too much going on in my little head to remember snipets of code!

Greatly appreciated!
Kevin

No problemo.

[NeoPa]

A (slightly) more flexible version which allows for varying lengths in the field [Test] would be :

UPDATE tblTest
SET [Test] = Left([Test],8) & "[Your Replacement]" & Mid([Test],12)
WHERE [Test] Is Not Null