By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Feb 20 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,602
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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest SET tblTest.Test = Left$([Test],8) & "<your 3-digit-value here>"   & Right$([Test],17)
  2. WHERE tblTest.Test Is Not Null;
Feb 20 '07 #2

P: 2
Assuming the Field containing the data is called [Test], and its length is a consistent 28 characters long, then:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest SET tblTest.Test = Left$([Test],8) & "<your 3-digit-value here>"   & Right$([Test],17)
  2. 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
Feb 21 '07 #3

ADezii
Expert 5K+
P: 8,602
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.
Feb 21 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
A (slightly) more flexible version which allows for varying lengths in the field [Test] would be :
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblTest
  2. SET [Test] = Left([Test],8) & "[Your Replacement]" & Mid([Test],12)
  3. WHERE [Test] Is Not Null
Feb 23 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.