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

how to update a field in database..?

P: 5
hai to all....!!
i want to upade a field in database.
iam using msaccess.
i know update query.
i have a field like this.
"D:\sample\test\new folder"

now i want to change the field like this.
D:\sample\aaaaaa\new folder

i want to change "test" as "aaaaaa"
is it posible to update a perticular part in a string?
i mean without updating the total filed, i want to update a perticular letters in that field.

please help me.
thank u very much.
Jan 25 '08 #1
Share this Question
Share on Google+
6 Replies


P: 32
Is it always the same letters that need replacing and what are they being replaced to? Is that always the same. Where is that info stored.

You can use an iif statement in a query with the instring "instr" expression, but if it is something that is not exactly the same each time it would get more complicated.
Jan 25 '08 #2

Delerna
Expert 100+
P: 1,134
Here is your fields value
"D:\sample\test\new folder"


In your update query you can update it like this
Replace(FieldName,"test","aaaaaa")

of course jyoung2's comments on changing text that varies still applies
Jan 25 '08 #3

P: 5
Here is your fields value
"D:\sample\test\new folder"


In your update query you can update it like this
Replace(FieldName,"test","aaaaaa")

of course jyoung2's comments on changing text that varies still applies
hai delerna...!
thank u very much for ur reply....
ok. this solution useful to me.
but my problem is i dont want to replace all fields which have "test".
suppose i have the fields in the table like this.
D:\sample\test\new folder
D:\sample\test
D:\sample\test\abcd
D:\sample\test\new folder\test\ewew


i want the output like this...
D:\sample\AAAA\new folder
D:\sample\AAAA
D:\sample\AAAA\abcd
D:\sample\AAAA\new folder\test\ewew


in the last filed it has another string "test".
D:\sample\test\new folder\test\ewew
i want to change the first test only.
D:\sample\AAAA\new folder\test\ewew

if i used Replace(FieldName,"test","aaaaaa") it replaces all test's in field.
but i want to replace first test only.
Jan 26 '08 #4

P: 32
you could expand your replace
D:\sample\test\

Replace(FieldName,"D:\sample\test\","D:\sample\AAA A\")

thedn it would replace the whole section. Alot of this depends on this gets populated but this would change the first test only



hai delerna...!
thank u very much for ur reply....
ok. this solution useful to me.
but my problem is i dont want to replace all fields which have "test".
suppose i have the fields in the table like this.
D:\sample\test\new folder
D:\sample\test
D:\sample\test\abcd
D:\sample\test\new folder\test\ewew


i want the output like this...
D:\sample\AAAA\new folder
D:\sample\AAAA
D:\sample\AAAA\abcd
D:\sample\AAAA\new folder\test\ewew


in the last filed it has another string "test".
D:\sample\test\new folder\test\ewew
i want to change the first test only.
D:\sample\AAAA\new folder\test\ewew

if i used Replace(FieldName,"test","aaaaaa") it replaces all test's in field.
but i want to replace first test only.
Jan 26 '08 #5

Delerna
Expert 100+
P: 1,134
Agree with Jyoung2. It all depends on how structured your strings are. Your strings appear to always following a structure D:\sample\test\............

If however you had
D:\AnotherFolder\test\new folder\test\ewew

I don't know off-hand if access has the function CHARINDEX() but I guess it would have something similar. I don't have access available at the moment

CHARINDEX returns the position within a string of the first occurrence of another string
So CHARINDEX("D:\AnotherFolder\test\new folder\test\ewew","test")

would return 17. You could then use some string manipulation magic to retrieve
everything to the left of 'test' and everything to the right.
Then you can concatenate LeftSide & "Replacement Text" & RightSide
Jan 26 '08 #6

Delerna
Expert 100+
P: 1,134
just put "ms access charindex" into google and it appears that
INSTR() is the access equivalent of CHARINDEX()
Jan 26 '08 #7

Post your reply

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