Find and replace text in table

[mike wild]

I have a table of 10 fields most with obscure info like:
!FC:\MP3S\TOP 40\Cry Little Sister - lost boys.mp3

And i am trying to strip "!FC:\MP3S\TOP 40\" out without much luck.

I have tried a few suggestion from past posts which through public functions.
I would like to say:
Go to table "addresses" and omit all ocurances of "!FC:\MP3S\TOP 40\" in the field named "song".

any help would be great

 

[dcx693]

Try using the function below in a calculated query column:

Function GetFileName(strFullname As String)
    Do Until (InStr(strFullname, "\")) = 0
        strFullname = Right(strFullname, Len(strFullname) - (InStr(strFullname, "\")))
    Loop
    GetFileName = strFullname
End Function

It was designed to extract the filename from a filename with a full path. It worked for the example you gave.

If you're always going to be just removing the same string:
!FC:\MP3S\TOP 40\
from the field, then it can be a bit simpler calculation (though you can still use the function above):
IIf(Left([yourfield],17)="!FC:\MP3S\TOP 40\",Mid([yourfield],18),[yourfield])

 

[mike wild]

yes the string is always the same

how would i assign the code below to work on a field on a table or would i somehow send it through a query?

IIf(Left([yourfield],17)="!FC:\MP3S\TOP 40\",Mid([yourfield],18),[yourfield])

 

[mike wild]

got it in an update query

suppose there were two strings to omit, both always the same string - how would they be nested?

 

[dcx693]

Why nest them? Why not do one, then the other?

 

[Kodo]

When you need to replace content, regular expressions are the way to go.
see my post here
http://www.access-programmers.co.uk...44&page=2&pp=15&highlight=regular+expressions
for an example.

 

[mike wild]

i am also trying to delete the first letter from the string, the letter varies.

what i have so far:

IIf(Left([Song],1)=Left([song],1),[Song])

but it does not work - nothing much in help

 

[dcx693]

Under what conditions are you trying to remove the 1st letter? Always? If so, update [myfield] to Mid([myfield],2)

 

[mike wild]

i now have this

IIf(Left([Song],1)=Mid([song],2),[Song])

and it just earses every thing in the song field

 

[mike wild]

Got it - Thanks very much for your hard work

 

[mike wild]

Not i need to omit a ")" which is at the end of the string. Any ideas?

i have this
IIf(Left([time],10)=")","",([time])))

but the whole thig deletes

 

[dcx693]

I see that we need some lessons in the use of string functions....

The IIf function works this way:
IIf(condition,value if condition is true, value if condition is false)

If you're looking to test if the final character in the string is ")", your condition is very wrong. Your function, as you've written it, is testing to see if the the 10 leftmost characters in [time] are equal to ")". If the ")" is potentially in the 10th character location, then use use Mid([time],10,1)=")".

What you need in the "value if condition is true" part is an expression that will chop off the last character of the string if it's equal to ")". Your function, as you've written it, would erase the entire string by setting it equal to "". Not what you want. To chop off the final character from a string that has 10 characters, use Left([time],9).

 

[mike wild]

thank you dcx693 for you help.

However, your suggestion did not work out - and i posted again since it was now totally different question to the original title