10 replies
by gary_w
SELECT Left([File],InStr(InStr([File],' -')+1,[File],' -')) AS DirName
FROM table1;
Gary
by gary_w
Oops, should be:
SELECT Left([File],InStr(InStr([File],' -')+2,[File],' -')) AS DirName
FROM table1;
by gary_w
A little cleaner version:
SELECT Left([File],InStrRev([File],' -')) AS DirName
from table1
by gary_w
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
by maricaibo
Excellent. Never heard of the InStrRev function before
by regg
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
by regg
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
by maricaibo
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending