Remove Characters before a certain part of a string

[MTSPEER]

Hello,

I have made it so my database inputs the name of the file that was imported into my table(PVE) and it goes into the field, FileName. The end of the string is what I want to save, the rest I want to remove. this is an example of the string: dhaskdnhasklds//sdasdas/PROD_MAD_0e3433.txt I want to remove everything before *PROD*. And the length of characters before PROD will change everytime. And the number of characters I want to keep after PROD, including PROD is 33. I was asking if anyone can help me solve removing those characters with the length changing everytime..except the PROD will always be there..thank you

[June7]

I count only 15 characters following PROD.

x represents the value.

Mid(x, InStr(x, "PROD")+4)

[MTSPEER]

I was just giving an example of what I was trying to say. I didn't want to give an actual file path. But there are 33 characters including PROD that I want to keep in the file path. and that is everything at the end after PROD. and the number of characters before PROD will change. so won't "x" need to change all the time?

[June7]

My example doesn't care how many characters precede or follow "PROD", just that PROD is always there.

X is just a variable to represent value in the formula. You could do this calc in a query and reference fieldname in place of x.

[MTSPEER]

Oh okay sorry I never did this before, didn't realize x was supposed to be the Field Name. Okay I got it to work. But PROD is just missing from it. I want to include PROD in the output

[June7]

Then remove the +4.

About string manipulation functions
http://www.techonthenet.com/access/functions/index.php
http://www.databasedev.co.uk/access-...functions.html

[MTSPEER]

Thank you very much! It works!

[ssanfu]

Here are three other examples that might help you understand how to use InStrRev() and Right() functions.

As Subs

Sub extraction1()
    'multi step
    Dim s As String
    Dim FN As String  'file name
    Dim pos As Integer
    Dim FN_Len As Integer

    FN = "dhaskdnhasklds//sdasdas/PROD_MAD_0e3433.txt"

    FN_Len = Len(FN)
    pos = InStrRev(FN, "/")

    s = Right(FN, FN_Len - pos)
    Msgbox "Filename is " & s

End Sub

Sub extraction2()
    'one line
    Dim s As String
    Dim FN As String  'file name

    FN = "dhaskdnhasklds//sdasdas/PROD_MAD_0e3433.txt"

    s = Right(FN, Len(FN) - InStrRev(FN, "/"))
    Msgbox "Filename is " & s

End Sub

As a function

Function extraction3(pFileName As String) As String
    'one line
    Dim FN As String

    FN = Right(pFileName, Len(pFileName) - InStrRev(pFileName, "/"))

    'return value
    extraction3 = FN

End Function