How to get FileName from FullPath with Jet SQL?

  • Thread starter Thread starter deko
  • Start date Start date
D

deko

I have an Access 2003 mdb with a table named 'tblDocuments' that contains
Full Paths, like this:

[FullPath]
C:\Users\Administrator\MyDirectory\SomeFile.pdf

I want Select and Alias FullPath to FileName like this:

SELECT Dir([FullPath]) As FileName
FROM tblDocuments
WHERE (criteria);

But the Dir function returns a null string if 'SomeFile.pdf' does not exist.

What function can I use in my query to get FileName from FullPath regardless
if 'SomeFile.pdf' exists or not?

Thanks in advance.
 
You can use the follwing function

Public Function GFileName(str As Variant) As Variant

If IsNull(str) Then Exit Function

GFileName = Mid(str, InStrRev(str, "\") + 1)

End Function

You could also use that expersison right in teh sql

eg: Mid([FieldName], InStrRev([FieldName], "\") + 1)
 
SELECT Mid([FullPath], InStrRev([FullPath], "\") + 1)
FROM tblDocument
WHERE (criteria)'
 
SELECT Mid([FullPath], InStrRev([FullPath], "\") + 1)
FROM tblDocument
WHERE (criteria)'
Click to expand...

yep, that did it. Thanks again.

SELECT [Folder], [DocumentPath], IIf([Folder], "\" + Mid([DocumentPath],
InStrRev([DocumentPath], "\") + 1), Mid([DocumentPath],
InStrRev([DocumentPath], "\") + 1)) AS Item, [Entity_ID], [Doc_ID]
FROM tblMaintenanceDocumentsMissing
WHERE ([Missing] = -1)
ORDER BY [DocumentPath];

The database has a document management feature where documents can be
"linked" to records. The Office File Dialog is used to pick both files and
folders to be associated with a database record and then these are displayed
in a datasheet. Clicking an item in the datasheet passes the path to
FollowHypelink which opens the document.

The problem is when things get moved around the filesystem. So I built a
form that displays missing files and /folders. the above query is the
recordsource for that form.

But first I have to create a temp table - and this is where I rely on the
Dir function:

SELECT DISTINCT [DocumentPath], [Folder], IIf([Folder], Dir([DocumentPath],
16), Dir([DocumentPath])) AS Document, [Doc_ID], [Entity_ID] INTO
tblMaintenanceDocumentsMissing
FROM tblDocuments;

If Len([Document]) = 0, then I have a missing Item. seems to be working
well so far, but it's a little weird asking a query to tell me what's on
disk.
 
Back
Top