Seperate a field into parts

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hello, and thanks! I have one field in the DB that is the
fully qualified path & file name. I would like to have an
expression look at the full path name then start from the
right until it gets to the first '\' that would seperate
the file name from the path. I have been looking inside
the expression builder but I do not see any functions that
will do an inspect looking for '\'.

Any and all help will be greatly appreciated.

Thanks again for you time!
 
Suggest that you handle this as a string expression with the MID function.
Create a loop on code that steps one character at a time from right to left
until you encounter the backslash character, store the position as a
variable, then extract the two strings path and filename.
 
Hello, and thanks! I have one field in the DB that is the
fully qualified path & file name. I would like to have an
expression look at the full path name then start from the
right until it gets to the first '\' that would seperate
the file name from the path. I have been looking inside
the expression builder but I do not see any functions that
will do an inspect looking for '\'.

Any and all help will be greatly appreciated.

Thanks again for you time!
Click to expand...

It will depend upon your version of Access.

If you're version has the InStrRev Function you can use that to find
the rightmost "\" character.

Public Function ParseFileName(StringIn As String) As String
Dim intX As Integer
StringIn = "c:\My Housejold\SomeFile\MyName\Here"
intX = InStrRev(StringIn, "\")
ParseFileName = Mid(StringIn, intX + 1)
End Function

Otherwise you will need to create your own User Defined Function and
use InStr() to find each "\" in turn until you reach the last one.

Public Function ParseFileName(StringIn as String) as String
Dim intX As Integer
Dim intY As Integer
intX = InStr(1, StringIn, "\")

Do While intX <> 0
intY = intX
intX = InStr(intX + 1, StringIn, "\")
Loop
ParseFileName = Mid(StringIn, intY + 1)

End Function

Add error handling to both of the above functions, as needed.
 
Back
Top