parse all text after the last \

[deb]

I have code that stores the path of a document as a link to the document in
the field called DocPath.

I need to take the text after the last \ to use as the document name and put
it into the textbox called DocName.

sample data is...
\\server1\folder1\folder2\folder3\folder4\thisdocname.pdf
\\server1\folder1\anotherdoc.doc
\\server2\folder1\folder2\thatdoc.xls
and so on
I need in the text box "DocName"...
thisdocname.pdf
anotherdoc.doc
thatdoc.xls

To recap..
I click a button, the file serach box pop usp and I find the document. The
path to the doc is stored in field called DocPath. I need to have the text
after the last \ to be displayed in the field called DocName.

Please help!! and thank you in advance!!!

 

[tedmi]

Dim i as Integer, j as Integer, sPath as String
sPath = DocPath
j = Len(sPath)
For i = j To 1 Step -1
If Mid(sPath, i, 1) = Chr(92) Then
DocName = Right(sPath, j-i)
Exit For
End If
Next

 

[Klatuu]

Very simple way:
Me.DocName = Mid([DocPath], Instrrev([DocPath], "\") +1)

 

[John Spencer]

One other alternative assuming that the doocuments actually exist.

DocName = Dir(DocPath)

That will return a null string if the document does not exist.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Very simple way:
Me.DocName = Mid([DocPath], Instrrev([DocPath], "\") +1)

I have code that stores the path of a document as a link to the document in
the field called DocPath.

I need to take the text after the last \ to use as the document name and
put
it into the textbox called DocName.

sample data is...
\\server1\folder1\folder2\folder3\folder4\thisdocname.pdf
\\server1\folder1\anotherdoc.doc
\\server2\folder1\folder2\thatdoc.xls
and so on
I need in the text box "DocName"...
thisdocname.pdf
anotherdoc.doc
thatdoc.xls

To recap..
I click a button, the file serach box pop usp and I find the document.
The
path to the doc is stored in field called DocPath. I need to have the
text
after the last \ to be displayed in the field called DocName.

Please help!! and thank you in advance!!!

 

[Clifford Bass]

Hi Deb,

Here is yet another way. Place the following function in a module and
add the "Microsoft Scripting Runtime" to your references (on Tools menu in
the VBA editor).

Public Function DocumentName(ByVal strDocumentPath As String) As String

Static fso As New FileSystemObject

DocumentName = fso.GetFileName(strDocumentPath)

End Function

You will now be able to call it from a query, from code, or from the
Control Source of a text box. For example you would place the following in
DocName's Control Source:

=DocumentName([DocPath])

Clifford Bass

 

[Douglas J. Steele]

While that does work as advertised, why bother introducing the overhead of
FSO when it's not required?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Deb,

Here is yet another way. Place the following function in a module and
add the "Microsoft Scripting Runtime" to your references (on Tools menu in
the VBA editor).

Public Function DocumentName(ByVal strDocumentPath As String) As String

Static fso As New FileSystemObject

DocumentName = fso.GetFileName(strDocumentPath)

End Function

You will now be able to call it from a query, from code, or from the
Control Source of a text box. For example you would place the following
in
DocName's Control Source:

=DocumentName([DocPath])

Clifford Bass

I have code that stores the path of a document as a link to the document
in
the field called DocPath.

I need to take the text after the last \ to use as the document name and
put
it into the textbox called DocName.

sample data is...
\\server1\folder1\folder2\folder3\folder4\thisdocname.pdf
\\server1\folder1\anotherdoc.doc
\\server2\folder1\folder2\thatdoc.xls
and so on
I need in the text box "DocName"...
thisdocname.pdf
anotherdoc.doc
thatdoc.xls

To recap..
I click a button, the file serach box pop usp and I find the document.
The
path to the doc is stored in field called DocPath. I need to have the
text
after the last \ to be displayed in the field called DocName.

Please help!! and thank you in advance!!!

 

[Clifford Bass]

Hi Doug,

Maybe because it uses one of Microsoft's presumedly approved functions
for dividing up the path information. Maybe because it deals with all kinds
of funky valid and invalid path information such as the use of forward
slashes instead of back slashes because your files are not stored on a
Microsoft Windows server? Maybe because it does not require that the file be
present or accessible when it is run? Maybe because the overhead is not
noticable on today's modern computers (a function call out to a 168KB dll)?
Maybe because it is far easier than creating your own function? Maybe
because you are already using the reference for other purposes? Just some
possibilities.

Sincerely,

Clifford Bass

 

[Klatuu]

I would disagree with many of your premises. I believe John Spencer's
method very good, minimal code, and reliable.

The only reason I would use mine rather than his is mine does not require
any disk access.

As to newer faster computers, it doesn't matter if a Lear Jet is carrying
10,000 lbs of extra freight, it will be slower that a Lear Jet without the
freight.

The FSO should be used prundently.

 

[Clifford Bass]

Hi Doug and Klatuu,

Your questions prompted me to do some testing from Access 2007 of a
relatively new and speedy computer. I wrote a short C program that would
time each method. I included the timing of starting up and closing out of
Access so as to include the time added by the loading and unloading of the
scripting DLL. (By the by, due to your questions I discovered that just
opening a simple database causes Access 2007 to access over 150 DLLs; most of
them system DLLs!)

Here is the code I used to test against actual files that I created.

==================================================

Public Function GetFileNameWrapper(ByRef strFullPath As String) As String

Static fso As New FileSystemObject

GetFileName = fso.GetFileName(strFullPath)

End Function

Public Function DoTheTest() As Boolean

Dim intIndex1 As Integer
Dim intIndex2 As Integer
Dim strFileName As String
Dim strFullPath As String

For intIndex2 = 1 To 10000
For intIndex1 = 1 To 250
strFullPath = "\\server2\share\Cliff\Folder1\" & intIndex1 &
".txt"
strFileName = GetFileNameWrapper(strFullPath)
strFullPath = "\\server1\files\Cliff\Testing\Folder3\" &
intIndex1 & ".txt"
strFileName = GetFileNameWrapper(strFullPath)
strFullPath = "\\server2\share\Cliff\Folder2\" & intIndex1 &
".txt"
strFileName = GetFileNameWrapper(strFullPath)
strFullPath = "\\server1\files\Cliff\Testing\Folder4\" &
intIndex1 & ".txt"
strFileName = GetFileNameWrapper(strFullPath)
Next intIndex1
Next intIndex2

DoTheTest = True

End Function

==================================================

Public Function DoTheTest() As Boolean

Dim intIndex1 As Integer
Dim intIndex2 As Integer
Dim strFileName As String
Dim strFullPath As String

For intIndex2 = 1 To 10000
For intIndex1 = 1 To 250
strFullPath = "\\server2\share\Cliff\Folder1\" & intIndex1 &
".txt"
strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
strFullPath = "\\server1\files\Cliff\Testing\Folder3\" &
intIndex1 & ".txt"
strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
strFullPath = "\\server2\share\Cliff\Folder2\" & intIndex1 &
".txt"
strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
strFullPath = "\\server1\files\Cliff\Testing\Folder4\" &
intIndex1 & ".txt"
strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
Next intIndex1
Next intIndex2

DoTheTest = True

End Function

==================================================

Public Function DoTheTest() As Boolean

Dim intIndex1 As Integer
Dim intIndex2 As Integer
Dim strFileName As String
Dim strFullPath As String

For intIndex2 = 1 To 200
For intIndex1 = 1 To 250
strFullPath = "\\server2\share\Cliff\Folder1\" & intIndex1 &
".txt"
strFileName = Dir(strFullPath)
strFullPath = "\\server1\files\Cliff\Testing\Folder3\" &
intIndex1 & ".txt"
strFileName = Dir(strFullPath)
strFullPath = "\\server2\share\Cliff\Folder2\" & intIndex1 &
".txt"
strFileName = Dir(strFullPath)
strFullPath = "\\server1\files\Cliff\Testing\Folder4\" &
intIndex1 & ".txt"
strFileName = Dir(strFullPath)
Next intIndex1
Next intIndex2

DoTheTest = True

End Function

==================================================

Here are the results. Note that I had to limit the number if iterations
using the Dir() function because it takes so much longer. Plus I did not
want to keep hammering the server.

Seconds elapsed using FileSystemObject.GetFileName() (10,000,000
iterations): 8.451
Seconds elapsed using Mid() and InStrRev() (10,000,000 iterations): 14.108
Seconds elapsed using Dir() (200,000 iterations): 124.124

Sincerely,

Clifford Bass

 

[Klatuu]

I will admit to being very surprised and puzzled as to how loading the fso
object and doing the disk access is faster than intrinsic functions.

Whether it would make any difference, I don't know, but in some similar
timing test (not involving fso) I found the results would vary depending on
the order in which the tests were executed. I found where the results were
very close between two of the three, the first test run would show a better
time than the second.

That is not to dispute your results, but rather to offer information for
validation.

Now I am compelled to set up my own testing using 2003.

Please understand my intention is not to argue, but to collaborate.

 

[Clifford Bass]

Hi Klatuu,

Here is the C program I used (more or less) if it is any help to you.
I created three separate MDB files, one to test each method. In each MDB I
created the AutoExec macro to run the code and then quit out of Access.
Initially I had tested it in the reverse order and then had the thought you
had about order. The order did not seem to make any significant difference.
The Dir() method results did not surprise me because that does actual server
disk accesses and the other two methods do not. I expected your method to be
a little faster than using a FileSystemObject.

=================================================
// ProgramTimer.c

#include <conio.h>
#include <process.h>
#include <stdio.h>
#include <time.h>

int main(int argc, char* argv[])
{
clock_t clStart;
clock_t clEnd;
double dblSeconds;

clStart = clock();
_spawnl(_P_WAIT, "C:\\Program Files\\Microsoft
Office\\Office12\\MSACCESS.EXE", "MSACCESS.EXE",
"D:\\ProgSrcs\\Access\\TestMSR.mdb", NULL);
clEnd = clock();
dblSeconds = (double) (clEnd - clStart) / (double) CLOCKS_PER_SEC;
printf("\nSeconds elapsed using FileSystemObject.GetFileName() (10,000,000
iterations): %1.3f", dblSeconds);

clStart = clock();
_spawnl(_P_WAIT, "C:\\Program Files\\Microsoft
Office\\Office12\\MSACCESS.EXE", "MSACCESS.EXE",
"D:\\ProgSrcs\\Access\\TestMid.mdb", NULL);
clEnd = clock();
dblSeconds = (double) (clEnd - clStart) / (double) CLOCKS_PER_SEC;
printf("\nSeconds elapsed using Mid() and InStrRev() (10,000,000
iterations): %1.3f", dblSeconds);

clStart = clock();
_spawnl(_P_WAIT, "C:\\Program Files\\Microsoft
Office\\Office12\\MSACCESS.EXE", "MSACCESS.EXE",
"D:\\ProgSrcs\\Access\\TestDir.mdb", NULL);
clEnd = clock();
dblSeconds = (double) (clEnd - clStart) / (double) CLOCKS_PER_SEC;
printf("\nSeconds elapsed using Dir() (200,000 iterations): %1.3f",
dblSeconds);

printf("\n\nPress any key to exit....\n");
_getch();

return 0;
}

=====================================================

Sincerely,

Clifford Bass

 

[Klatuu]

Thanks

Did I understand you to say the fso does not require disk access?
How then does it get its directory and file information?

Hi Klatuu,

Here is the C program I used (more or less) if it is any help to you.
I created three separate MDB files, one to test each method. In each MDB
I
created the AutoExec macro to run the code and then quit out of Access.
Initially I had tested it in the reverse order and then had the thought
you
had about order. The order did not seem to make any significant
difference.
The Dir() method results did not surprise me because that does actual
server
disk accesses and the other two methods do not. I expected your method to
be
a little faster than using a FileSystemObject.

=================================================
// ProgramTimer.c

#include <conio.h>
#include <process.h>
#include <stdio.h>
#include <time.h>

int main(int argc, char* argv[])
{
clock_t clStart;
clock_t clEnd;
double dblSeconds;

clStart = clock();
_spawnl(_P_WAIT, "C:\\Program Files\\Microsoft
Office\\Office12\\MSACCESS.EXE", "MSACCESS.EXE",
"D:\\ProgSrcs\\Access\\TestMSR.mdb", NULL);
clEnd = clock();
dblSeconds = (double) (clEnd - clStart) / (double) CLOCKS_PER_SEC;
printf("\nSeconds elapsed using FileSystemObject.GetFileName() (10,000,000
iterations): %1.3f", dblSeconds);

clStart = clock();
_spawnl(_P_WAIT, "C:\\Program Files\\Microsoft
Office\\Office12\\MSACCESS.EXE", "MSACCESS.EXE",
"D:\\ProgSrcs\\Access\\TestMid.mdb", NULL);
clEnd = clock();
dblSeconds = (double) (clEnd - clStart) / (double) CLOCKS_PER_SEC;
printf("\nSeconds elapsed using Mid() and InStrRev() (10,000,000
iterations): %1.3f", dblSeconds);

clStart = clock();
_spawnl(_P_WAIT, "C:\\Program Files\\Microsoft
Office\\Office12\\MSACCESS.EXE", "MSACCESS.EXE",
"D:\\ProgSrcs\\Access\\TestDir.mdb", NULL);
clEnd = clock();
dblSeconds = (double) (clEnd - clStart) / (double) CLOCKS_PER_SEC;
printf("\nSeconds elapsed using Dir() (200,000 iterations): %1.3f",
dblSeconds);

printf("\n\nPress any key to exit....\n");
_getch();

return 0;
}

=====================================================

Sincerely,

Clifford Bass

I will admit to being very surprised and puzzled as to how loading the
fso
object and doing the disk access is faster than intrinsic functions.

Whether it would make any difference, I don't know, but in some similar
timing test (not involving fso) I found the results would vary depending
on
the order in which the tests were executed. I found where the results
were
very close between two of the three, the first test run would show a
better
time than the second.

That is not to dispute your results, but rather to offer information for
validation.

Now I am compelled to set up my own testing using 2003.

Please understand my intention is not to argue, but to collaborate.

 

[Clifford Bass]

Hi Klatuu,

My statement was based on a test I happened to do with a non-existent
path and file. Something like:

MsgBox fso.GetFileName("C:\abc.def\ghi.jkl")

It returned "ghi.jkl". So it was just parsing the name--no disk access.

Clifford