Many variations of this will exist throughout the table. What I need to do is pick out all the unique occurances of the variation upto folder 1. Keep in mind the generic names I use here are replaced by may different combinations of names so I essentially need to be able to search and stop on the N slash. In this case the third one with nothing past it.
I placed you data in table named table5 then I used this code
SELECT InStr(1,[Table5]![fileshare_Path],"folder1") AS Count_to_folder1, Left([Table5]![fileshare_Path],[Count_to_folder1]+7) AS Expr1
If you past the code into SQL View and look at it in design view you will understand it better at least I would. It seemed to work , I put in several combinations and always came up with what I think you are looking for.
I think you have it right but thats not how I did it
Actually I created a query and counted the number of character starting after the first two \\ and including the next \ I then removed that part and counted to the fourth and removed that, then counted to the 5th when the numbers are added I used the right function for that count.