vba code needed for path insert

Hi Guys , this is my first post (so be gentle)!

I have an access table .. its my wifes karaoke catalogue.

i have to change field 3 and i think there must be a way to do this rather than manually....

heres my problem

field 1 : song title
field 2: artist
field 3 : disc number (example : SFG002-01)

field 3 records run like :

SFG002-02
SFG002-03
SFG002-04


i want to change this field :
from SFG002-02 to
C:\KARAOKE\SF002\SFG002-02

from SFG010-02 to
C:\KARAOKE\SF010\SFG010-02

from SFG100-02 to
C:\KARAOKE\SF100\SFG100-02

what i want is vba code that can "read" the 3 digit number after the G in SFG and insert a relevant path before the catalogue number : c:\karaoke\sf***\

i could copy /paste and manually change but theres loads!
im also very curious as to if it can be done, i find myself with a similar task every few months...


hoping some1 can help..

thanks

davie.

NewField3 = "C:\KARAOKE\SF" & mid(Field3,3,3) & "\" & Field3

S-

Thanks S

this code looks excellent,

do i put it in a macro then run?
or can i use an update query?

I woud use this portion

"C:\KARAOKE\SF" & mid(Field3,3,3) & "\" & Field3

as an expression in an update query

S-

Put your code in an update statement and ran it:

instead of :SFG002-02

i now have : C:\KARAOKE\SFeld\Field3

am i missing something/doing the update wrong?

YES

"C:\KARAOKE\SF" & mid(Field3,3,3) & "\" & Field3

Field3 - is the name of the field that the existing information is being stored. You have to change that to reflect your system


S-

i changed the field3 to path

"C:\KARAOKE\SF" & Mid("path",3,3) & "\" & "path"

heres what i got:

C:\KARAOKE\SFth\path

if i change it to karaokep :

"C:\KARAOKE\SF" & Mid("karaokep",3,3) & "\" & "karaokep"

C:\KARAOKE\SFrao\karaokep


my method is to create an update query,
field : karaokep
table: sunfly
update to : "C:\KARAOKE\SF" & Mid("karaokep",3,3) & "\" & "karaokep"


i see what the query is doing - reading the actual text instead of the field contents......

Change the " " to []

S-

query must have at least one destination field.......

"C:\KARAOKE\SF" & Mid([Field3],3,3) & "\" & [Field3]

think ur getting there

Send in the SQL code of your update statement


(SQL view of your query)
S-

thanks sbaxter,

u got me there in the end and it saved me loads of copy/paste/find/replace!!

one thing -

UPDATE sunfly SET sunfly.path3 = "C:\KARAOKE\SF" & Mid([path3],3,3) & "\" & [path3];

gave me
C:\KARAOKE\SFG01\SFG011-11

character missing - sfg01 should have been sfg011

so i changed mid 3,3 to 3,4 and it was fine..

why is this?

Mid([path3],3,3)

The first three says "what position do I start in"

The second three says "go over 3 spaces"


The original question only gave example that need three so that is why I gave you 3 up it sounds like it works with 4 so good luck

S-