search/replace

[gibsonsgman]

alright, i have an interesting question. I am attempting to make a
music database that will automatically format all of my songs for me so
i don't have to perform the tedious task of doing it myself. What i am
stuck on now is if i have a filename for example "01 Little Wing.mp3" I
want access to remove the "01 " part of the filename. Is this
possible? Any help would be greatly appreciated.

[Lyle Fairfield]

[e-mail] wrote:

alright, i have an interesting question. I am attempting to make a
music database that will automatically format all of my songs for me so
i don't have to perform the tedious task of doing it myself. What i am
stuck on now is if i have a filename for example "01 Little Wing.mp3" I
want access to remove the "01 " part of the filename. Is this
possible? Any help would be greatly appreciated.

Assuming "01 Little Wing.mp3" is in the Current Directory
you could excute this code:

Name "01 Little Wing.mp3" As "Little Wing.mp3"

or, if not in current directory

Name "C:\Documents and Settings\NauseousMusic\01 Little Wing.mp3" As
"C:\Documents and Settings\NauseousMusic\Little Wing.mp3"

Now is that what you want to do?
Who knows?

Maybe you just want code to lop off the first three characters of a
string?
Mid$("01 Little Wing.mp3", 4)

Maybe you want to remove the first "word"?
Mid$("01 Little Wing.mp3", InStr("01 Little Wing.mp3", " ") + 1)

Maybe it's beginning numerals?
Dim s As String
s = "01 Little Wing.mp3"
While IsNumeric(Mid$(s, 1, 1))
s = Mid$(s, 2)
Wend
s = Trim(s)

Maybe it's a combination.

Most likely, it's something else entirely as I am not a good guesser.

[gibsonsgman]

sorry if i wasn't clear on what i was trying to do. I want to run
through an entire list in a table and remove the track number. Not all
of them have track numbers so i also want to test to see if the track
number is there. THe track number will always consist of two digits
followed by a space. This is the section of text i want to remove.

[Lyle Fairfield]

gi*********@gmail.com wrote in news:1155124668.427182.113590
@m79g2000cwm.googlegroups.com:

sorry if i wasn't clear on what i was trying to do. I want to run
through an entire list in a table and remove the track number. Not all
of them have track numbers so i also want to test to see if the track
number is there. THe track number will always consist of two digits
followed by a space. This is the section of text i want to remove.

I tried to post an answer in Google Grooups a few minutes ago but it
seems to be caught in an infinite loop somewhere in the ether.

So I'll try again.

Before you try anything, please, make a safety copy of your table/data.

1. Cut and paste these two proceudres into a Standard Module:

Public Function WithoutTrackNumber( _
ByVal SongTitle) As String
WithoutTrackNumber = Nz(SongTitle, "")
While IsNumeric(Mid$(WithoutTrackNumber, 1, 1))
WithoutTrackNumber = Mid$(WithoutTrackNumber, 2)
Wend
WithoutTrackNumber = Trim(WithoutTrackNumber)
End Function

Private Sub ScratchtheTrackNumbers()
CurrentDb().Execute _
"UPDATE TableSongTitles " _
& "SET SongTitle = " _
& "WithoutTrackNumber([SongTitle])"
End Sub

Change the table and field names in the second procedure to match your
table and field names. Don't change anything in the fist procedure.

Save the module.

Run the second procedure. Just in case you don't know how to do this,
here is what works for me.
I make sure the cursor is in the procedure I want to run, in this case
the Private Sub. Then I click on the Green Arrow in theStandard Toolbar
at the top. Provided the Procedure has no required parameters it should
run.

*******
Don't forget:
Before you try anything, please, make a safety copy of your table/data.
*******

But if you are doing this every day for a year you may wish to discard
the Private sub, cut and paste the first procedure (the Public Function)
into as Standard Module and save the module, and paste this SQL string
into a query in SQL view in the Query Builder (and save it) so that you
can just run it. Of course, as before you would have to change the table
and file names to match yours.

UPDATE TableSongTitles SET SongTitle = WithoutTrackNumber([SongTitle])

*******

Did I mention?
Before you try anything, please, make a safety copy of your table/data.

--
Lyle Fairfield

[gibsonsgman]

Thanks, i have not tried it out yet, but reading through the code it
all seems to make sense. It seems like it should work, and that was
what I needed.