<path> for <path><filename>

[Chris]

Hi
I am trying to create a query to group pathnames when all I have in my
source table is a fully qualified filename.
ie I have a record that looks like:
\\Slug\media\Audio\Music\MP3-LameEnc\Amici Forever\The Opera Band\07
Nimrod-Lux Aeterna.mp3
and I am trying to group by just the path name. I am looking for an
expression that will strip off the filename.
Any ideas please.
Thanks
Chris

 

[John Spencer (MVP)]

What version of Access? If later than Access 2000, you can use the InStrRev
function to get the position of the last slash and then use Left to get
everything before that.

PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))

Then you can group by that expression. IF you are using an earlier version of
Access then you will need to use a custom user function to do this. In Access
2000 all you need to do is to write a wrapper function, since VBA has InStrRev
available. That would be something like:

Public Function MyInstrRev(strIN As String, strMatch As String, _
Optional Lngstart As Long = -1, _
Optional intCompare As Integer = 0)

MyInstrRev = InStrRev(strIN, strMatch, Lngstart, intCompare)

End Function

 

[Graham R Seach]

Chris,

There's also this:
strMyPathOnly = Replace(strFilePath, Dir(strFilePath, vbNormal), "")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Graham R Seach]

Just realised I was in the Queries newsgroup. In that case:

....in Access 2000 and above:
PathOnly: Replace([FilePath], Dir([FilePath], 1), "")

....in prior versions:
PathOnly: Repl([FilePath], Dir([FilePath], 1), "")
....then grab the Repl() function from
http://www.pacificdb.com.au/MVP/Code/repl.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Chris]

Thanks to you both. That fixed my problem of grouping by CD's. I am
currently using another app(MP3 observer) to poplulate my source table of
MP3 files I had searched for some VBScript to work direct from access but
was unsuccessful but did find something to do the job from Excel. Are
either of you aware of any public code for access that searches directories
and populates a table with MP3 tag info?
Hopefully ...
Chris

 

[Graham R Seach]

Chris,

What are you ultimately trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Chris]

Hi Graham
I am ripping my CD collection to a disk attached to my network and playing
the collection through TwonkyVision running on a Linksys NSLU2. I would
like my MP3 collection to be cataloged in a database so that I can add my
own comments, export my own playlists etc. Mediaplayer and iTunes will do
some of this for me but not enough so I am looking for an access applicaton
that will recurse through specified directories reading and storing the MP3
tags as it comes across music files.
Any ideas?
Hopefully ...
Chris

 

[Graham R Seach]

Chris,

Sorry for taking so long to get back to you. I've been really busy. The
following will do it:

Public Sub GetFileListFromDir(sDir As String, sType As String)
'Get a list of specific file types from a specified directory
'NOTE: sType must be in the format ".mpeg" or ".mp3"
'
Dim db As Database
Dim sFile As String
Dim sSQL As String

Set db = CurrentDb

'Append "\", otherwise it won't work.
If Right(sDir, 1) <> "\" Then sDir = sDir & "\"

'Get the first filename
sFile = Dir(sDir & "*" & sType, vbNormal)
'Continue as long as there's something to get
Do While sFile <> ""
'NOTE: If you want to store the path and filename
'separately, use the following SQL String.
sSQL = "INSERT INTO tblMyTable " & _
"(FilePath, FileName) " & _
"VALUES (""" & sDir & """, """ & sFile & """)"

'NOTE: If you want to store the whole path in one field,
'use the following SQL string instead of the previous one.
'sSQL = "INSERT INTO tblMyTable " & _
"(FilePath) " & _
"VALUES (""" & sDir & sFile & """)"

'Retrieve another filename
sFile = Dir
Loop

'Clean up
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Chris]

Hi Graham
Thank you so much for this sample code. Unfortunately however, this code
populated a db table with file names and file paths, I is the MP3 Tag info
held in the files that I was really after ...
I know it can be done as I have some Excel code that can do it but I can't
understand the code! I am a beginner. I was hoping that someone may have
already cracked this chestnut as was willing to share.
Kind regards
Chris

 

[Graham R Seach]

Chris,

It seems I didn't understand what you're after. What's the MP3 Tag info?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Chris]

Hi Graham
MP3 files (and mosst other music files) contain artist, album, track name,
year, etc within the file This is the information that is displayed when
music files are played back. More information on MP3 tag info at
http://www.id3.org/intro.html.
Have a great weekend.
Chris

 

[Douglas J. Steele]

Are you looking for the ID3v1.x tag information, or the ID3v2 tag
information?

I've got code for the ID3v1.x tag info as part of my November, 2004 Access
Answers column in Pinnacle Publication's Smart Access. You can download the
column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

I also wrote about getting the ID3v2 tag info in the December, 2004 issue of
Access Advisor, but unfortunately I haven't got around to posting that
anywhere yet.

 

[Chris]

Hi Doug
Yes that is what I was looking for except that the module would recurse
through directories (from a given root) and add file path and MP3 tag info
to a table rather than output on a form. Oh and v2 info would be
preferable. I was hoping that someone would have already done this and made
the code public. I would very happily do it myself but I am not able! I
can do a bit of VBA in Excel and that's my lot.
Hopefully again ....
Chris

 

[Douglas J. Steele]

That's my June, 2005 article in Access Advisor. Don't know how easy Access
Advisor is to find in the UK, but it should be on the newsstands now. I
can't remember, though, whether 100% of the code is in the article, and I'm
not sure you can download the samples unless you're a subscriber.
Unfortunately, I don't believe I'm allowed publish the code myself while the
magazine's on the stands...

 

[Chris]

Hi Doug

So close and yet so far! I actually once did subscribe to Access Advisor
but it was all too advanced for me. I had a look at a web only subscription
but that is still $99. For the time being I guess that I'll continue to
uses my Excel solution. Thank you very much for the posts. I will check
back from time to time to see if the code if available.
..
Cheers

Chris

 

[Chris]

Hello John/Group

I included the function
"PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))"
into a query and this gave me exactly what I was looking for ... BUT ...when
I try to grab the records from this query into an Excel workbook using odbc
I get the following error:
[Microsoft][ODBC Microsoft Access Driver]Undefined function 'InStrRev'
in expression.
Any ideas please?

Thanks
Chris

 

[[MVP] S.Clark]

InStrRev is a very Access specific feature, and probably cannot be used
outside of Access.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Hello John/Group

I included the function
"PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))"
into a query and this gave me exactly what I was looking for ... BUT
...when I try to grab the records from this query into an Excel workbook
using odbc I get the following error:
[Microsoft][ODBC Microsoft Access Driver]Undefined function 'InStrRev'
in expression.
Any ideas please?

Thanks
Chris

What version of Access? If later than Access 2000, you can use the
InStrRev
function to get the position of the last slash and then use Left to get
everything before that.

PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))

Then you can group by that expression. IF you are using an earlier
version of
Access then you will need to use a custom user function to do this. In
Access
2000 all you need to do is to write a wrapper function, since VBA has
InStrRev
available. That would be something like:

Public Function MyInstrRev(strIN As String, strMatch As String, _
Optional Lngstart As Long = -1, _
Optional intCompare As Integer = 0)

MyInstrRev = InStrRev(strIN, strMatch, Lngstart, intCompare)

End Function

 

[Chris]

Thanks for that Steve. Is there a function that will work for me with
Access2003 and ODBC?
Hopefully
Chris

InStrRev is a very Access specific feature, and probably cannot be used
outside of Access.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Hello John/Group

I included the function
"PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))"
into a query and this gave me exactly what I was looking for ... BUT
...when I try to grab the records from this query into an Excel workbook
using odbc I get the following error:
[Microsoft][ODBC Microsoft Access Driver]Undefined function 'InStrRev'
in expression.
Any ideas please?

Thanks
Chris

What version of Access? If later than Access 2000, you can use the
InStrRev
function to get the position of the last slash and then use Left to get
everything before that.

PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))

Then you can group by that expression. IF you are using an earlier
version of
Access then you will need to use a custom user function to do this. In
Access
2000 all you need to do is to write a wrapper function, since VBA has
InStrRev
available. That would be something like:

Public Function MyInstrRev(strIN As String, strMatch As String, _
Optional Lngstart As Long = -1, _
Optional intCompare As Integer = 0)

MyInstrRev = InStrRev(strIN, strMatch, Lngstart, intCompare)

End Function

 

[[MVP] S.Clark]

I think the long way around the barn is that at the time of storing the
data, you would need to write the desired value into a different
column.(Assuming this can be coded from whatever UI there is.) Then you
query the value straight from the table.

Thanks for that Steve. Is there a function that will work for me with
Access2003 and ODBC?
Hopefully
Chris

InStrRev is a very Access specific feature, and probably cannot be used
outside of Access.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Hello John/Group

I included the function
"PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))"
into a query and this gave me exactly what I was looking for ... BUT
...when I try to grab the records from this query into an Excel workbook
using odbc I get the following error:
[Microsoft][ODBC Microsoft Access Driver]Undefined function
'InStrRev' in expression.
Any ideas please?

Thanks
Chris





What version of Access? If later than Access 2000, you can use the
InStrRev
function to get the position of the last slash and then use Left to get
everything before that.

PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))

Then you can group by that expression. IF you are using an earlier
version of
Access then you will need to use a custom user function to do this. In
Access
2000 all you need to do is to write a wrapper function, since VBA has
InStrRev
available. That would be something like:

Public Function MyInstrRev(strIN As String, strMatch As String, _
Optional Lngstart As Long = -1, _
Optional intCompare As Integer = 0)

MyInstrRev = InStrRev(strIN, strMatch, Lngstart, intCompare)

End Function

 

[Bambi]

Thank you for that Steve. Unfortunately it isn't my database, it's part of
a great mp3 application MediaMonkey. I guess that Ican create a table from
the query. Not quite what I wanted but good enough!
Cheers
Chris

I think the long way around the barn is that at the time of storing the
data, you would need to write the desired value into a different
column.(Assuming this can be coded from whatever UI there is.) Then you
query the value straight from the table.

Thanks for that Steve. Is there a function that will work for me with
Access2003 and ODBC?
Hopefully
Chris

InStrRev is a very Access specific feature, and probably cannot be used
outside of Access.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting


Hello John/Group

I included the function
"PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))"
into a query and this gave me exactly what I was looking for ... BUT
...when I try to grab the records from this query into an Excel
workbook using odbc I get the following error:
[Microsoft][ODBC Microsoft Access Driver]Undefined function
'InStrRev' in expression.
Any ideas please?

Thanks
Chris





What version of Access? If later than Access 2000, you can use the
InStrRev
function to get the position of the last slash and then use Left to
get
everything before that.

PathOnly: LEFT([PathField],InStrRev([PathField],"\",-1,0))

Then you can group by that expression. IF you are using an earlier
version of
Access then you will need to use a custom user function to do this.
In Access
2000 all you need to do is to write a wrapper function, since VBA has
InStrRev
available. That would be something like:

Public Function MyInstrRev(strIN As String, strMatch As String, _
Optional Lngstart As Long = -1, _
Optional intCompare As Integer = 0)

MyInstrRev = InStrRev(strIN, strMatch, Lngstart, intCompare)

End Function