General discussion

  • Creator
    Topic
  • #2176879

    Access: Find 2nd occurence of a string

    Locked

    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?

All Comments

  • Author
    Replies
    • #3334930

      Reply To: Access: Find 2nd occurence of a string

      by gary_w ·

      In reply to Access: Find 2nd occurence of a string

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

      Gary

    • #3330407

      Reply To: Access: Find 2nd occurence of a string

      by regg ·

      In reply to Access: Find 2nd occurence of a string

      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

    • #3330405

      Reply To: Access: Find 2nd occurence of a string

      by regg ·

      In reply to Access: Find 2nd occurence of a string

      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

    • #3352234

      Reply To: Access: Find 2nd occurence of a string

      by maricaibo ·

      In reply to Access: Find 2nd occurence of a string

      This question was closed by the author

Viewing 3 reply threads