extract part of a file path with mid function

[Mike]

I wanted to extract the filename from a file path that is imported in
a table. I created a query and entered

startposition: Len([filename])-(Len([filename])-InStrRev([filename],"\"))<< and
endposition: Len([filename])-(Len([filename])-InStrRev([filename],"."))<<

in seperate columns of the query.

Then I tried to use

Expr1: Mid([filename],[startposition],[endposition])<<

I received the start of the filename, but still had the filename
extension.

If I use

left([filename],[endposition])<<

I get the full path up to the "." of the file extension.

This looks to me as if it is possible to strip a path only once in a
query column with a combined function.

Can anybody confirm this, or is there a way to get this all in one
query column?

Thanks in advance.

 

[KARL DEWEY]

Try this ---
Expr1:
Mid([filename],(Len([filename])-(Len([filename])-InStrRev([filename],"\"))),
(Len([filename])-(Len([filename])-InStrRev([filename],"."))))

You can not use an alias in the same query that creates that alias.
--
KARL DEWEY
Build a little - Test a little

I wanted to extract the filename from a file path that is imported in
a table. I created a query and entered

startposition: Len([filename])-(Len([filename])-InStrRev([filename],"\"))<< and
endposition: Len([filename])-(Len([filename])-InStrRev([filename],"."))<<

in seperate columns of the query.

Then I tried to use

Expr1: Mid([filename],[startposition],[endposition])<<

I received the start of the filename, but still had the filename
extension.

If I use

left([filename],[endposition])<<

I get the full path up to the "." of the file extension.

This looks to me as if it is possible to strip a path only once in a
query column with a combined function.

Can anybody confirm this, or is there a way to get this all in one
query column?

Thanks in advance.

 

[storrboy]

Easiest is likely to be making a function and calling it in the query
as a calculated field. I you need help with it, don't forget to say
what version you are using.

 

[Mike]

Try this ---
Expr1:
Mid([filename],(Len([filename])-(Len([filename])-InStrRev([filename],"\")))­,
(Len([filename])-(Len([filename])-InStrRev([filename],"."))))

You can not use an alias in the same query that creates that alias.
--
KARL DEWEY
Build a little - Test a little

I wanted to extract the filename from a file path that is imported in
a table. I created a query and entered

startposition: Len([filename])-(Len([filename])-InStrRev([filename],"\"))<< and
endposition: Len([filename])-(Len([filename])-InStrRev([filename],"."))<<

in seperate columns of the query.

Then I tried to use

Expr1: Mid([filename],[startposition],[endposition])<<

I received the start of the filename, but still had the filename
extension.

If I use

left([filename],[endposition])<<

I get the full path up to the "." of the file extension.

This looks to me as if it is possible to strip a path only once in a
query column with a combined function.

Can anybody confirm this, or is there a way to get this all in one
query column?

Thanks in advance.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

I tried that again, although I thought that I would also have had that
during my office hours today. The result is still as stated in my
first post. The mid function did only provide the first part of the
path stripping. The second part seems just to be ignored.

 

[Mike]

Try this ---
Expr1:
Mid([filename],(Len([filename])-(Len([filename])-InStrRev([filename],"\")))­­,
(Len([filename])-(Len([filename])-InStrRev([filename],"."))))

You can not use an alias in the same query that creates that alias.

I wanted to extract the filename from a filepaththat is imported in
a table. I created a query and entered
startposition: Len([filename])-(Len([filename])-InStrRev([filename],"\"))<<
and
endposition: Len([filename])-(Len([filename])-InStrRev([filename],".."))<<
in seperate columns of the query.
Then I tried to use
Expr1: Mid([filename],[startposition],[endposition])<<
I received the start of the filename, but still had the filename
extension.
If I use
left([filename],[endposition])<<
I get the fullpathup to the "." of the file extension.
This looks to me as if it is possible tostripapathonly once in a
query column with a combined function.
Can anybody confirm this, or is there a way to get this all in one
query column?
Thanks in advance.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

I tried that again, although I thought that I would also have had that
during my office hours today. The result is still as stated in my
first post. The mid function did only provide the first part of thepathstripping. The second part seems just to be ignored.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

I've got it so far (even with some workaround). Now I try to set a
dynamic filepath. Therefore I created a table filepath with a field
filepath. I'd like the module to LookIn the table to get the
filepath. Is there a way to do so?

this is the module

Function LocateFile(strFileName As String)
Dim vItem As Variant
Dim db As DAO.Database
Set db = CurrentDb
With Application.FileSearch
.FileName = strFileName
.LookIn = >>> This is the place where I want the module to fetch
the filepath from the table<<<
.SearchSubFolders = False
.Execute

CurrentDb.Execute "DELETE * from Import_songs_tbl",
dbFailOnError

For Each vItem In .FoundFiles
db.Execute _
"INSERT INTO Import_songs_tbl (Filename) " & _
"VALUES(" & Chr(34) & vItem & Chr(34) & ")", _
dbFailOnError
Next vItem
End With
MsgBox "Done.", vbInformation
Set db = Nothing
End Function

 

[storrboy]

I'm sorry but I'm really not following what you are trying to do.
Maybe I'm just brain-dead this week. If it helps at all, here are a
couple functions to divide up a file path that you can call from a
query or other code. One returns the file name and extension the other
returns the path not including the file. These as so far tested work
for URLs as well. I see you are trying to start the FileSearch tool at
a certain point, but I'm not sure how this relates to the original
post. Maybe the second function will give that starting point. Add the
following to a module and call as needed.

(By the way, I was guessing that your version of Access supports the
Split() function.)
(Lines wrapped to try and retain proper syntax)

'----Start of
Code----------------------------------------------------------------
Private Declare Function PathIsURL Lib "shlwapi.dll" Alias _
"PathIsURLA" (ByVal pszPath As String) As Long

Function GetFileName(strPath As String) As String
On Error GoTo stoprun
Dim aryPath() As String
Dim strDelim As String

GetFileName = ""
If PathIsURL(strPath) Then
strDelim = "/"
Else
strDelim = "\"
End If

aryPath = Split(strPath, strDelim, , _
vbTextCompare)
GetFileName = aryPath(UBound(aryPath))

Exit_Here:
Exit Function

stoprun:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here
End Function

Function GetFileFolder(strPath) As String
On Error GoTo stoprun
Dim svPath As String
Dim aryPath() As String
Dim strDelim As String
Dim a As Long
GetFileFolder = ""

If PathIsURL(strPath) Then
strDelim = "/"
Else
strDelim = "\"
End If
aryPath = Split(strPath, strDelim, , _
vbTextCompare)

For a = 0 To UBound(aryPath) - 1
svPath = svPath & aryPath(a) & strDelim
Next a

GetFileFolder = svPath

Exit_Here:
Exit Function

stoprun:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here
End Function

'----End of
Code----------------------------------------------------------------