Access: Find 2nd occurence of a string - TechRepublic
General discussion
March 1, 2005 at 09:31 AM
maricaibo

Access: Find 2nd occurence of a string

by maricaibo . Updated 21 years, 3 months ago

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 discussion is locked

All Comments