String query

[GSevensM]

Hi all,

I've been handed a spread sheet that is an output of information captured by a script that captures network drives attached to a user's profile. An example of the output, for one user, is below:

K: = \\Server01\MARKETING ; P: = \\Server01\APPLICATIONS ; R: = \\Server02\ENGINEERING ; S: = \\Server01\COMPANY SHARE\another share ; U: = \\Server01\users$\user01 ; Z: = \\Server02\Engineering ;

What I need to do is split that down into 4 columns to go into another table. So I need to split it down by:

Driver letter: e.g. K:
Server Name: e.g. Server01
Share without the drive letter: e.g. \\Server01\COMPANY SHARE\another subshare\

I am stumped so any help would be gratefully received!

 

[Minty]

Have a look here http://www.access-programmers.co.uk/forums/showthread.php?t=138393 for a similar problem and solution.

 

[GSevensM]

Thanks Minty but that's left me even more confused as it seems to be just finding spaces and not splitting out like I require.

 

[Minty]

You will need to use a combination of the functions shown in the link and Len and Mid and Left string functions to pull out the data you need.

As a starter for you to get going with, assuming your string is called txtNetDrive
your drive letter will always be returned with (if you are in the query design window);

sDriveLetter: Left(txtNetDrive,2)

Next to return your Server name you need the 6th character to the 3rd occurance of \
That's where the link comes in.

Play around with it and let us know where you get stuck.

 

[GSevensM]

Thanks Minty, my fault but I don't think I am making my problem clear. If it was just one instance of the below I can split that out OK already.

Z: = \\Server01\Customer Support ;

My problem is the way the script has generated the output is that the below is all generated in one excel cell and some only have one folder after the server share whilst others go deeper into several subfolders which I don't want them to split down to.

K: = \\Server01\MARKETING ; P: = \\Server01\APPLICATIONS ;

I think I may have to come up with some Excel wizardry prior to importing into Access to split each network drive out into its own cell.

Thanks anyway!

 

[Minty]

Ah so you need to split each ; separated string out as a record, then perform the detail split...
Quick and dirty kludge personally I would replace each ; with a CRLF to give you a line per record text list

If not you will need to build a function to loop through the string and put each string into a new record, it may as well do the rest of the string break down as well.

I've not got the time to write it for you at the moment I'm afraid, although the linked thread is a starting point with the later functions adapted a bit.

 

[jdraw]

I agree with Minty in that the Split( ) function will easily breakup your list of servers at the ";".

See this link http://www.access-programmers.co.uk/forums/showthread.php?t=271776&highlight=split