Find and replace text in table

mike wild

Registered User.
Local time
Today, 20:51
Joined
Feb 16, 2003
Messages
69
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
 
Try using the function below in a calculated query column:
Code:
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])
 
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])
 
got it in an update query

suppose there were two strings to omit, both always the same string - how would they be nested?
 
Why nest them? Why not do one, then the other?
 
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
 
Under what conditions are you trying to remove the 1st letter? Always? If so, update [myfield] to Mid([myfield],2)
 
i now have this

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

and it just earses every thing in the song field
 
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
 
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).
 
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
 

Users who are viewing this thread

Back
Top Bottom