General discussion

Locked

Access: Find 2nd occurence of a string

By Maricaibo ·
In a field named 'File' in MS Access XP I have text representing 'Artist - Album - Title'. I need to pull everything left of the second ' - ' string to create a directory path named Artist\Album. I can use "SELECT Left([File],InStr([File],' -')) AS DirName" but that pulls from just the first occurence. I need left of the SECOND occurence. For example, from a file named "Waylon Jennings - Honky Tonk Heroes - Omaha.mp3" I would want to use VBA to create a directory path named "Waylon Jennings\Honky Tonk Heroes". Ideas, anyone?

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by Gary_W In reply to Access: Find 2nd occurenc ...

SELECT Left([File],InStr(InStr([File],' -')+1,[File],' -')) AS DirName
FROM table1;


Gary

Collapse -

by Gary_W In reply to

Oops, should be:

SELECT Left([File],InStr(InStr([File],' -')+2,[File],' -')) AS DirName
FROM table1;

Collapse -

by Gary_W In reply to

A little cleaner version:

SELECT Left([File],InStrRev([File],' -')) AS DirName
from table1

Collapse -

by Gary_W In reply to

Even better:

SELECT Left([File],InStr([File],' -')-1) & '\' & mid([File], InStr([File],' -')+3, InStrRev([File],' -') - InStr([File],' -')-3) AS DirName
from table1

Ok, that's it! I'm going home.
Gary

Collapse -

by Maricaibo In reply to

Excellent. Never heard of the InStrRev function before

Collapse -

by Regg In reply to Access: Find 2nd occurenc ...

So then you will need to again parse the results to create the directories.

I would first of all suggest changing the DB Table to have 3 different fields to store the 3 different segments - it'll save alot of headaches latter.

Until then, I would just pull the complete contents of the field "as-is" and in a Sub Proceedure do the parsing as needed and create the folders:

Public Sub ParseFieldXYZ(FieldValue As String)
'Sample: FieldValue = "Waylon Jennings - Honky Tonk Heroes - Omaha.mp3"


End Sub

Collapse -

by Regg In reply to Access: Find 2nd occurenc ...

Now, How did that happen: I only hit the Space bar:


So then you will need to again parse the results to create the directories.

I would first of all suggest changing the DB Table to have 3 different fields to store the 3 different segments - it'll save alot of headaches latter.

Until then, I would just pull the complete contents of the field "as-is" and in a Sub Proceedure do the parsing as needed and create the folders.
Create a recordset and loop throught the records, passing the value of the field in question to the following proceedure:

Public Sub ParseFieldXYZ(ByVal FieldValue As Variant, Optional BasePath = "C:\MySongs")
'FieldValue uses a Variant in case the field contains a NULL
'Sample: FieldValue = "Waylon Jennings - Honky Tonk Heroes - Omaha.mp3"
'Change the optional parameter to the initial base path which you use. Under this path the Sub folders will be created

Dim oFSO As Object 'Scripting.FileSystemObject
Dim ary() As String
Dim vElement As Variant
Dim sPath As String

Screen.MousePointer = vbHourglass

FieldValue = FieldValue & vbNullString 'in case the field contains a NULL
'Or
'FieldValue = Format$(FieldValue)

If Len(FieldValue) Then
Set oFSO = CreateObject("Scripting.FileSystemObject")

sPath = BasePath
If Not oFSO.FolderExists(sPath) Then oFSO.CreateFolder (sPath)

FieldValue = Mid$(FieldValue, 1, InStrRev(FieldValue, "-") - 1)

ary = Split(FieldValue, "-")
For Each vElement In ary
sPath = sPath & "\" & Trim$(vElement)
If Not oFSO.FolderExists(sPath) Then oFSO.CreateFolder (sPath)

Next vElement

Else
'Error
End If

Set oFSO = Nothing

ExitHere:
Screen.MousePointer = vbDefault
Exit Sub

ErrHere:
MsgBox Err.Number & vbCr & Err.Description
Resume

Collapse -

by Regg In reply to

'The rest got cut off:

ErrHere:
MsgBox Err.Number & vbCr & Err.Description
Resume ExitHere
End Sub

Collapse -

by Maricaibo In reply to

Great! Tahnks!

Collapse -

by Maricaibo In reply to Access: Find 2nd occurenc ...

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums