How to get FileName from FullPath with Jet SQL?

[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.

 

[Albert D. Kallal]

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)

 

[Douglas J. Steele]

SELECT Mid([FullPath], InStrRev([FullPath], "\") + 1)
FROM tblDocument
WHERE (criteria)'

 

[deko]

SELECT Mid([FullPath], InStrRev([FullPath], "\") + 1)

FROM tblDocument
WHERE (criteria)'

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.