Extract portion of field

[jb]

I would like to extract a portion of this field, that has varying numbers of
characters. The one consistency is that the information I need is only 5
characters and is immediately following the 6th "/" in each case. Sample
information is shown below:

jjjj/aaa/aa.bbbbbccc/dddd/eeee/53414/55791/gjtuirioepw
jjjj//aaaaa.bbbbbccc/ddd/eeeeeee/12324/98765/gjtuiriovmnfenbm


In this example, I would like to see the result as 55791 and 98765
respectively. Any suggestions would be much appreciated.

jb

 

[raskew via AccessMonster.com]

Take a look at this method for locating the nth repetition of a character in
a string:

http://www.access-programmers.co.uk/forums/showthread.php?t=156440

HTH - Bob

 

[jb]

This method does look like it will work, however, I am not real familiar with
Access 2007 and limited in my Visual Basic expertise, so I am wondering where
I would put this method, so that it is saved and available to use in my
queries as a user-defined function. - jb

 

[raskew via AccessMonster.com]

Copy/paste the function to a standard module then call it as shown in the
examples.

Here's an example:

x = "jjjj/aaa/aa.bbbbbccc/dddd/eeee/53414/55791/gjtuirioepw"
? mid(x, fFindNthOccur(x, "/", 6)+1, 5)
55791

Bob

This method does look like it will work, however, I am not real familiar with
Access 2007 and limited in my Visual Basic expertise, so I am wondering where
I would put this method, so that it is saved and available to use in my
queries as a user-defined function. - jb

Take a look at this method for locating the nth repetition of a character in
a string:

[quoted text clipped - 16 lines]

 

[jb]

Wow! Thank you so much. The function is working perfectly! I so appreciate
all that you experts do for us on these newsgroups. - jb

Copy/paste the function to a standard module then call it as shown in the
examples.

Here's an example:

x = "jjjj/aaa/aa.bbbbbccc/dddd/eeee/53414/55791/gjtuirioepw"
? mid(x, fFindNthOccur(x, "/", 6)+1, 5)
55791

Bob

This method does look like it will work, however, I am not real familiar with
Access 2007 and limited in my Visual Basic expertise, so I am wondering where
I would put this method, so that it is saved and available to use in my
queries as a user-defined function. - jb

Take a look at this method for locating the nth repetition of a character in
a string:

[quoted text clipped - 16 lines]

jb