Expression to pick out data between two slashes

[JohnLee]

Good morning Folks,

I'm looking for some help on how to build an expression that extracts some data that can be of varying lengths between two slashes please see below example data

=================

File SearchFolderDateG:\01 Archiving\Instant Take Ups Cashless Batched (CC&DD)\20110712\Gloria Bonnell\326E0395-DF75-44E4-B95B-8D1623222144.TIFInstant Take Ups Cashless Batched (CC&DD)20110712

File SearchFolderDateG:\01 Archiving\Instant Take Ups Cashless Batched (CC&DD)\20110712\Deb Morrissey\DE5D1AE1-86C8-4C9E-9F3F-DEC53C8E82C0.TIFInstant Take Ups Cashless Batched (CC&DD)20110712

File SearchFolderDateG:\01 Archiving\Instant Take Ups Cashless Batched (CC&DD)\20110712\Cath Archer\C0820254-B317-4C6F-93C3-9F7AE784BF5C.TIFInstant Take Ups Cashless Batched (CC&DD)20110712

=====================

So the data I want to pull out into a seperate column is the persons name i.e.
Cath Archer
Deb Morrisey
Gloria Bonnell

I think that the Mid function is what is needed here, but I can't work out how to use this function to target the data between the slash at the front of the persons name and the slash at the end of the persons name.

I know that the front slash will always have the starting position of 68 but I don't know what the ending position of the slash at the end of the persons name will be because each persons name is of a different length.

So I have something like this in the field row of my query:

Name: Mid([strFPath],68, ****) This astrisk is what I need to resolve.

Any help would be greatfully appreciated.

John

 

[spikepl]

http://www.developerbarn.com/microsoft-access/103-string-functions-listed-name.html

 

[JohnLee]

Good morning Spikepl,

Thanks for the pointer most helpful indeed, worked out a game plan from that, needs a some tidying up, but keep working on it.

Thanks again

John

 

[Galaxiom]

I would be more inclined to use a custom function that involved the Split() function to pasde the string into the sections between the slashes.

Then analyse these parts or choose the nth section. Otherwise you are very dependent on those folder names not changing length.

 

[JohnLee]

Hi GaloimAtHome,

Thanks for your advice, I actually worked out an expression that has given me the results that I was looking for as follows:

Name: Replace(Mid([strFPath],68,InStr(10,[strFPath],"\")),"\","")

This works because the strFPath is acutally onle consists of the following:

G:\01 Archiving\Instant Take Ups Cashless Batched (CC&DD)\20110712\Lorna Matt\

What I did in my first post was to show the strFPath and strFName concatenated, which is what I want for another aspect of my project, so by only targetting the strFPath part I was able to get the result I was looking for in the expression above.

So I get the following:

Chris Arnold
Cath Archer
Deb Morrissey
Gloria Bonnell
etc etc

Which is what I want to achieve.

As always the help from this forum is outstanding.

Thanks Guys for your pointers and advice.

John

 

[spikepl]

#4 & #5

Note that #4 has a point: it can be dangerous to assume that things as fleeting as a folder name remain unchanged, and thus that the position of the information you seek is fixed.

 

[JohnLee]

Hi Spikepl,

Thanks noted.

John

 

[Galaxiom]

I actually worked out an expression that has given me the results that I was looking for as follows:

Name: Replace(Mid([strFPath],68,InStr(10,[strFPath],"\")),"\","")

That working by luck alone.

Since you actually want the characters at the end of the string you are better off using the InStrRev function to find the slash. The following will take the characters from the last backslash that is not the final character then remove the slashes.

Replace(Mid([strPath], InstrRev(strFPath, "\", Len(strFPath)-1)),"\","")

There are many variants on this possibility.

BTW Name is a reserved word and best avoided for field and object names.

 

[JohnLee]

Hi GalaxiomAtHome,

Thanks for the observations, I have changed the Name field to UserName accordingly and your expression works great thanks most appreciated.

John