EXtracting substring from a string

[pderwael]

Hi folks,




I was using Excel to manage quite a large music DB when I hit the hard limit of Excel of about 1M lines, so I have no other option than switch to Access
As I'm not quite an Access expert, this is when I'm starting to tear my hair off


One of my DB fields contains paths and I need to extract the directory part of it
There are 3 types of strings:


M:\~Check\Artist - Year - Album\Track - Title
M:\~Queue\Artist - Year - Album\Track - Title
M:\R\Artist - Year - Album\Track - Title


With the builder, I have set the following formula:


IIf(Mid([Tracklist_Import]![Path],4,1)="~",Mid([Tracklist_Import]![Path],5,5),Mid([Tracklist_Import]![Path],4,1))


For the 3 above lines, in my understanding, this should return
Check
Queue
R


But this actually returns the following:
~Check\Artist - Year - Album\Track - Title
~Queue\Artist - Year - Album\Track - Title
R\Artist - Year - Album\Track - Title


Any advice would be appreciated!


Thanks a lot!

[kd2017]

The formula along with the data you provided works as expected on my end...



By the way, PATH is a reserved word. You should avoid giving fields or anything else a name that's a reserved word: http://allenbrowne.com/AppIssueBadWord.html

[pderwael]

Weird (to say the least...)


I have changed the "Path" to "Paath" just in case this would make any difference (didn't make any)


As I have just upgraded my home PC to Win 11 (with Access 2016), I thought this could be related to the Windows version
So, I tried on my work PC which is running Win 10 & Office 365: no difference either


I guess I will have to work it around by using lots of string functions, even if this is not the cleanest way forward...

[pderwael]

Issue finally solved

SOlution found in this thread:
https://www.accessforums.net/showthread.php?t=78802

All I had to do was to change the list separator in my regional settings from comma to semicolon

I now "just" have to pay attention to other exports which might be impacted by this change as well!

[Welshgasman]

Issue finally solved

SOlution found in this thread:
https://www.accessforums.net/showthread.php?t=78802

All I had to do was to change the list separator in my regional settings from comma to semicolon

I now "just" have to pay attention to other exports which might be impacted by this change as well!

Nicely found.
Not seen that problem before