how to update a field in database..?

[keerthisreenu]

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.

[jyoung2]

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.

[Delerna]

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

[keerthisreenu]

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.

[jyoung2]

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.

[Delerna]

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

[Delerna]

just put "ms access charindex" into google and it appears that
INSTR() is the access equivalent of CHARINDEX()