Strings and Things

[BrianPaul]

I have a table that was generated from a function thanks to Tom Watson I
belive. When I run my table it gives the full path to my files which was
collected and displays the following in my EBook Table. EbookID (primary
key) and Ebook.

In the Ebook field.

N:\Ebooks\Sports\how to golf.pdf
N:\Ebooks\Sports\Putting.pdf
N:\Ebooks\Sports\Driving the golf ball.pdf

Now I based A querry on the above table and sorted ascending.
However, I would love to go to the format property in the query of the field
Ebook and strip of the N:\Ebooks\Sports\

when I ran the query:
How to golf.pdf
putting.pdf
driving the golf ball.pdf

I would ask how to strip the file extension but I believe I would be asking
to much.

Thanks, I Learn so much from this forum

 

[Albert D. Kallal]

You probably could build a expression in the query builder that would give
you want you want.

However, that expresson is going to look ugly, and worse I have a
smaller/weaker brain and that type of expression is going to be diffuilt to
write. However, in code...it is easy. So, I going to suggest that you place
the following function in a public module..and it will give you the results
you want...

Public Function fStrip(str As Variant) As Variant

If IsNull(str) = True Then Exit Function

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

fStrip = Left(fStrip, InStr(fStrip, ".") - 1)


End Function

Now, in any report, or query, you can use

fstrip([NameOfField])

The above will return just the file name without the extension, or the
path...

 

[Larry Linson]

I have a table that was generated from
a function thanks to Tom Watson I
belive. When I run my table it gives the
full path to my files which was collected
and displays the following in my EBook
Table. EbookID (primary key) and Ebook.

In the Ebook field.

N:\Ebooks\Sports\how to golf.pdf
N:\Ebooks\Sports\Putting.pdf
N:\Ebooks\Sports\Driving the golf ball.pdf

Now I based A querry on the above table
and sorted ascending. However, I would
love to go to the format property in the
query of the field Ebook and strip of the
N:\Ebooks\Sports\

I'm not sure what you mean by "go to the format property in the query" but
have offered an approach to accomplish what you describe that you want to
do.

when I ran the query:
How to golf.pdf
putting.pdf
driving the golf ball.pdf

I would ask how to strip the file extension
but I believe I would be asking to much.

Not at all... it's no more trouble than removing the path.

To remove both the path and the file extension, create a Public Function in
a standard Module, as follows, and use it in your Query to create a
calculated Field, in the Query grid, that would read:

strBookName: RemPathAndExt([EBook])

Here is the function:

Public Function RemPathAndExt(pstrPathFileExt As String) As String
' Purpose: Remove the Path and File Extension from a fully-qualified
' disk address
Dim strFileExt As String 'Work variable, used for clarity
strFileExt = Right(pstrPathFileExt, Len(pstrPathFileExt) -
InStrRev(pstrPathFileExt, "\"))
RemPathAndExt = Left(strFileExt, Len(strFileExt) - 4)
End Function

Note: If I recall correctly, the InStrRev function was introduced in Access
2000. If you are using an earlier version, additional code would be required
to simulate the InStrRev functionality.

Larry Linson
Microsoft Access MVP

 

[BrianPaul]

Thanks Guys Will give it a try

I have a table that was generated from
a function thanks to Tom Watson I
belive. When I run my table it gives the
full path to my files which was collected
and displays the following in my EBook
Table. EbookID (primary key) and Ebook.

In the Ebook field.

N:\Ebooks\Sports\how to golf.pdf
N:\Ebooks\Sports\Putting.pdf
N:\Ebooks\Sports\Driving the golf ball.pdf

Now I based A querry on the above table
and sorted ascending. However, I would
love to go to the format property in the
query of the field Ebook and strip of the
N:\Ebooks\Sports\

I'm not sure what you mean by "go to the format property in the query" but
have offered an approach to accomplish what you describe that you want to
do.

when I ran the query:
How to golf.pdf
putting.pdf
driving the golf ball.pdf

I would ask how to strip the file extension
but I believe I would be asking to much.

Not at all... it's no more trouble than removing the path.

To remove both the path and the file extension, create a Public Function in
a standard Module, as follows, and use it in your Query to create a
calculated Field, in the Query grid, that would read:

strBookName: RemPathAndExt([EBook])

Here is the function:

Public Function RemPathAndExt(pstrPathFileExt As String) As String
' Purpose: Remove the Path and File Extension from a fully-qualified
' disk address
Dim strFileExt As String 'Work variable, used for clarity
strFileExt = Right(pstrPathFileExt, Len(pstrPathFileExt) -
InStrRev(pstrPathFileExt, "\"))
RemPathAndExt = Left(strFileExt, Len(strFileExt) - 4)
End Function

Note: If I recall correctly, the InStrRev function was introduced in Access
2000. If you are using an earlier version, additional code would be required
to simulate the InStrRev functionality.

Larry Linson
Microsoft Access MVP